﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.IO;
using System.IO.Compression;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace Shuba.Web.ws
{
    /// <summary>
    /// Summary description for wsb4
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class wsb4 : System.Web.Services.WebService
    {
        DataSet TransformIntoDataSet(byte[] value)
        {
            using (MemoryStream memStream = new MemoryStream(value))
            {
                using (GZipStream unzipStream = new GZipStream(memStream, CompressionMode.Decompress))
                {
                    DataSet objDataSet = new DataSet();
                    objDataSet.ReadXml(unzipStream, XmlReadMode.ReadSchema);
                    unzipStream.Close();
                    memStream.Close();
                    return objDataSet;
                }
            }
        }

        byte[] TransformIntoBytes(DataSet ds)
        {
            byte[] data;
            MemoryStream memStream = new MemoryStream();
            GZipStream zipStream = new GZipStream(memStream, CompressionMode.Compress);
            ds.WriteXml(zipStream, XmlWriteMode.WriteSchema);
            zipStream.Close();
            data = memStream.ToArray();
            memStream.Close();
            return data;
        }

        bool IsValidUser(string LDAPUser, string LDAPPass)
        {
            return true;
        }

        [WebMethod]
        public string UploadCIF(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                        UPDATE TBL_PROSPECT_CIF SET 
                             PROSPECT_CIF_ID = @PROSPECT_CIF_ID
                            ,LOB = @LOB
                            ,APPLICATION_ID = @APPLICATION_ID
                            ,UNIT_CODE = @UNIT_CODE
                            ,AO_CODE = @AO_CODE
                            ,CIF_NUMBER = @CIF_NUMBER
                            ,FULL_NAME = @FULL_NAME
                            ,ALIAS_NAME = @ALIAS_NAME
                            ,SHORT_NAME = @SHORT_NAME
                            ,GENDER = @GENDER
                            ,FIRST_NAME = @FIRST_NAME
                            ,MIDLE_NAME = @MIDLE_NAME
                            ,LAST_NAME = @LAST_NAME
                            ,MOTHER_NAME = @MOTHER_NAME
                            ,MARITAL_STATUS = @MARITAL_STATUS
                            ,RESIDENCE_PHONE = @RESIDENCE_PHONE
                            ,MOBILE_PHONE = @MOBILE_PHONE
                            ,PLACE_OF_BIRTH = @PLACE_OF_BIRTH
                            ,DATE_OF_BIRTH = @DATE_OF_BIRTH
                            ,HOME_STATUS = @HOME_STATUS
                            ,CUSTOMER_IC = @CUSTOMER_IC
                            ,IC_EXPIRY_DATE = @IC_EXPIRY_DATE
                            ,MONTHLY_INCOME = @MONTHLY_INCOME
                            ,NUMBER_OF_DEPENDENTS = @NUMBER_OF_DEPENDENTS
                            ,MAIL_ADDRESS_1 = @MAIL_ADDRESS_1
                            ,MAIL_ADDRESS_2 = @MAIL_ADDRESS_2
                            ,MAIL_ADDRESS_3 = @MAIL_ADDRESS_3
                            ,MAIL_ZIP_CODE = @MAIL_ZIP_CODE
                            ,PERMANENT_ADDRESS_1 = @PERMANENT_ADDRESS_1
                            ,PERMANENT_ADDRESS_2 = @PERMANENT_ADDRESS_2
                            ,PERMANENT_ADDRESS_3 = @PERMANENT_ADDRESS_3
                            ,PERMANENT_ZIP_CODE = @PERMANENT_ZIP_CODE
                            ,HOLD_MAIL_ADDRESS_1 = @HOLD_MAIL_ADDRESS_1
                            ,HOLD_MAIL_ADDRESS_2 = @HOLD_MAIL_ADDRESS_2
                            ,HOLD_MAIL_ADDRESS_3 = @HOLD_MAIL_ADDRESS_3
                            ,HOLD_MAIL_ZIP_CODE = @HOLD_MAIL_ZIP_CODE
                            ,IS_REQUEST = @IS_REQUEST
                            ,PROGRESS_STATUS = @PROGRESS_STATUS
                            ,REFFERAL = @REFFERAL
                            ,APPROVAL = @APPROVAL
                            ,MAINTAIN = @MAINTAIN
                            ,ENTRY_BY = @ENTRY_BY
                            ,ENTRY_DATE = @ENTRY_DATE
                            ,UPDATE_BY = @UPDATE_BY
                            ,UPDATE_DATE = @UPDATE_DATE
                            ,UPLOAD_BY = @UPLOAD_BY
                            ,UPLOAD_DATE = @UPLOAD_DATE
                            ,IS_UPLOAD = @IS_UPLOAD
                            WHERE (PROSPECT_CIF_ID = @PROSPECT_CIF_ID)
                        IF @@ROWCOUNT = 0
                        INSERT INTO TBL_PROSPECT_CIF(PROSPECT_CIF_ID
                            ,LOB
                            ,APPLICATION_ID
                            ,UNIT_CODE
                            ,AO_CODE
                            ,CIF_NUMBER
                            ,FULL_NAME
                            ,ALIAS_NAME
                            ,SHORT_NAME
                            ,GENDER
                            ,FIRST_NAME
                            ,MIDLE_NAME
                            ,LAST_NAME
                            ,MOTHER_NAME
                            ,MARITAL_STATUS
                            ,RESIDENCE_PHONE
                            ,MOBILE_PHONE
                            ,PLACE_OF_BIRTH
                            ,DATE_OF_BIRTH
                            ,HOME_STATUS
                            ,CUSTOMER_IC
                            ,IC_EXPIRY_DATE
                            ,MONTHLY_INCOME
                            ,NUMBER_OF_DEPENDENTS
                            ,MAIL_ADDRESS_1
                            ,MAIL_ADDRESS_2
                            ,MAIL_ADDRESS_3
                            ,MAIL_ZIP_CODE
                            ,PERMANENT_ADDRESS_1
                            ,PERMANENT_ADDRESS_2
                            ,PERMANENT_ADDRESS_3
                            ,PERMANENT_ZIP_CODE
                            ,HOLD_MAIL_ADDRESS_1
                            ,HOLD_MAIL_ADDRESS_2
                            ,HOLD_MAIL_ADDRESS_3
                            ,HOLD_MAIL_ZIP_CODE
                            ,IS_REQUEST
                            ,PROGRESS_STATUS
                            ,REFFERAL
                            ,APPROVAL
                            ,MAINTAIN
                            ,ENTRY_BY
                            ,ENTRY_DATE
                            ,UPDATE_BY
                            ,UPDATE_DATE
                            ,UPLOAD_BY
                            ,UPLOAD_DATE
                            ,IS_UPLOAD
                            ) 
                            VALUES(@PROSPECT_CIF_ID
                            ,@LOB
                            ,@APPLICATION_ID
                            ,@UNIT_CODE
                            ,@AO_CODE
                            ,@CIF_NUMBER
                            ,@FULL_NAME
                            ,@ALIAS_NAME
                            ,@SHORT_NAME
                            ,@GENDER
                            ,@FIRST_NAME
                            ,@MIDLE_NAME
                            ,@LAST_NAME
                            ,@MOTHER_NAME
                            ,@MARITAL_STATUS
                            ,@RESIDENCE_PHONE
                            ,@MOBILE_PHONE
                            ,@PLACE_OF_BIRTH
                            ,@DATE_OF_BIRTH
                            ,@HOME_STATUS
                            ,@CUSTOMER_IC
                            ,@IC_EXPIRY_DATE
                            ,@MONTHLY_INCOME
                            ,@NUMBER_OF_DEPENDENTS
                            ,@MAIL_ADDRESS_1
                            ,@MAIL_ADDRESS_2
                            ,@MAIL_ADDRESS_3
                            ,@MAIL_ZIP_CODE
                            ,@PERMANENT_ADDRESS_1
                            ,@PERMANENT_ADDRESS_2
                            ,@PERMANENT_ADDRESS_3
                            ,@PERMANENT_ZIP_CODE
                            ,@HOLD_MAIL_ADDRESS_1
                            ,@HOLD_MAIL_ADDRESS_2
                            ,@HOLD_MAIL_ADDRESS_3
                            ,@HOLD_MAIL_ZIP_CODE
                            ,@IS_REQUEST
                            ,@PROGRESS_STATUS
                            ,@REFFERAL
                            ,@APPROVAL
                            ,@MAINTAIN
                            ,@ENTRY_BY
                            ,@ENTRY_DATE
                            ,@UPDATE_BY
                            ,@UPDATE_DATE
                            ,@UPLOAD_BY
                            ,@UPLOAD_DATE
                            ,@IS_UPLOAD
                            );");

                        db.AddInParameter(cmd, "@PROSPECT_CIF_ID", DbType.String, Convert.ToString(dr["PROSPECT_CIF_ID"]));
                        db.AddInParameter(cmd, "@LOB", DbType.String, Convert.ToString(dr["LOB"]));
                        db.AddInParameter(cmd, "@APPLICATION_ID", DbType.String, Convert.ToString(dr["APPLICATION_ID"]));
                        db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                        db.AddInParameter(cmd, "@AO_CODE", DbType.String, Convert.ToString(dr["AO_CODE"]));
                        db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int64, Convert.ToInt64(dr["CIF_NUMBER"]));
                        db.AddInParameter(cmd, "@FULL_NAME", DbType.String, Convert.ToString(dr["FULL_NAME"]));
                        db.AddInParameter(cmd, "@ALIAS_NAME", DbType.String, Convert.ToString(dr["ALIAS_NAME"]));
                        db.AddInParameter(cmd, "@SHORT_NAME", DbType.String, Convert.ToString(dr["SHORT_NAME"]));
                        db.AddInParameter(cmd, "@GENDER", DbType.String, Convert.ToString(dr["GENDER"]));
                        db.AddInParameter(cmd, "@FIRST_NAME", DbType.String, Convert.ToString(dr["FIRST_NAME"]));
                        db.AddInParameter(cmd, "@MIDLE_NAME", DbType.String, Convert.ToString(dr["MIDLE_NAME"]));
                        db.AddInParameter(cmd, "@LAST_NAME", DbType.String, Convert.ToString(dr["LAST_NAME"]));
                        db.AddInParameter(cmd, "@MOTHER_NAME", DbType.String, Convert.ToString(dr["MOTHER_NAME"]));
                        db.AddInParameter(cmd, "@MARITAL_STATUS", DbType.String, Convert.ToString(dr["MARITAL_STATUS"]));
                        db.AddInParameter(cmd, "@RESIDENCE_PHONE", DbType.String, Convert.ToString(dr["RESIDENCE_PHONE"]));
                        db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, Convert.ToString(dr["MOBILE_PHONE"]));
                        db.AddInParameter(cmd, "@PLACE_OF_BIRTH", DbType.String, Convert.ToString(dr["PLACE_OF_BIRTH"]));
                        db.AddInParameter(cmd, "@DATE_OF_BIRTH", DbType.DateTime, Convert.ToDateTime(dr["DATE_OF_BIRTH"]));
                        db.AddInParameter(cmd, "@HOME_STATUS", DbType.String, Convert.ToString(dr["HOME_STATUS"]));
                        db.AddInParameter(cmd, "@CUSTOMER_IC", DbType.String, Convert.ToString(dr["CUSTOMER_IC"]));
                        db.AddInParameter(cmd, "@IC_EXPIRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["IC_EXPIRY_DATE"]));
                        db.AddInParameter(cmd, "@MONTHLY_INCOME", DbType.Double, Convert.ToDouble(dr["MONTHLY_INCOME"]));
                        db.AddInParameter(cmd, "@NUMBER_OF_DEPENDENTS", DbType.Int16, Convert.ToInt16(dr["NUMBER_OF_DEPENDENTS"]));
                        db.AddInParameter(cmd, "@MAIL_ADDRESS_1", DbType.String, Convert.ToString(dr["MAIL_ADDRESS_1"]));
                        db.AddInParameter(cmd, "@MAIL_ADDRESS_2", DbType.String, Convert.ToString(dr["MAIL_ADDRESS_2"]));
                        db.AddInParameter(cmd, "@MAIL_ADDRESS_3", DbType.String, Convert.ToString(dr["MAIL_ADDRESS_3"]));
                        db.AddInParameter(cmd, "@MAIL_ZIP_CODE", DbType.String, Convert.ToString(dr["MAIL_ZIP_CODE"]));
                        db.AddInParameter(cmd, "@PERMANENT_ADDRESS_1", DbType.String, Convert.ToString(dr["PERMANENT_ADDRESS_1"]));
                        db.AddInParameter(cmd, "@PERMANENT_ADDRESS_2", DbType.String, Convert.ToString(dr["PERMANENT_ADDRESS_2"]));
                        db.AddInParameter(cmd, "@PERMANENT_ADDRESS_3", DbType.String, Convert.ToString(dr["PERMANENT_ADDRESS_3"]));
                        db.AddInParameter(cmd, "@PERMANENT_ZIP_CODE", DbType.String, Convert.ToString(dr["PERMANENT_ZIP_CODE"]));
                        db.AddInParameter(cmd, "@HOLD_MAIL_ADDRESS_1", DbType.String, Convert.ToString(dr["HOLD_MAIL_ADDRESS_1"]));
                        db.AddInParameter(cmd, "@HOLD_MAIL_ADDRESS_2", DbType.String, Convert.ToString(dr["HOLD_MAIL_ADDRESS_2"]));
                        db.AddInParameter(cmd, "@HOLD_MAIL_ADDRESS_3", DbType.String, Convert.ToString(dr["HOLD_MAIL_ADDRESS_3"]));
                        db.AddInParameter(cmd, "@HOLD_MAIL_ZIP_CODE", DbType.String, Convert.ToString(dr["HOLD_MAIL_ZIP_CODE"]));
                        db.AddInParameter(cmd, "@IS_REQUEST", DbType.String, Convert.ToString(dr["IS_REQUEST"]));
                        db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, Convert.ToString(dr["PROGRESS_STATUS"]));
                        db.AddInParameter(cmd, "@REFFERAL", DbType.String, Convert.ToString(dr["REFFERAL"]));
                        db.AddInParameter(cmd, "@APPROVAL", DbType.String, Convert.ToString(dr["APPROVAL"]));
                        db.AddInParameter(cmd, "@MAINTAIN", DbType.String, Convert.ToString(dr["MAINTAIN"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, Convert.ToString(dr["UPLOAD_BY"]));
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPLOAD_DATE"]));
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");

                        if (db.ExecuteNonQuery(cmd) > 0)
                        {
                            result++;
                        }
                    }
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string RequestCIF(string nip, string pass, byte[] data)
        {
            if (IsValidUser(nip, pass))
            {
                DataSet MyDataSet = TransformIntoDataSet(data);
                if (ValidateDataSetEmpty(MyDataSet))
                {
                    Database db = DBConn.CreateDatabase();
                    DataRow dr = MyDataSet.Tables[0].Rows[0];

                    DbCommand cmd = db.GetSqlStringCommand(@"SELECT COUNT(0) FROM TBL_PROSPECT_CIF
                    WHERE JOB_STATUS = 'RUN' AND PROSPECT_CIF_ID = @PROSPECT_CIF_ID");

                    db.AddInParameter(cmd, "@PROSPECT_CIF_ID", DbType.String, Convert.ToString(dr["PROSPECT_CIF_ID"]));

                    object result = db.ExecuteScalar(cmd);
                    if (Convert.ToInt32(result) > 0) return "1";

                    cmd = db.GetSqlStringCommand(@"
                        UPDATE TBL_PROSPECT_CIF SET
                             JOB_STATUS = 'RESEND'
                            ,PROSPECT_CIF_ID = @PROSPECT_CIF_ID
                            ,LOB = @LOB
                            ,APPLICATION_ID = @APPLICATION_ID
                            ,UNIT_CODE = @UNIT_CODE
                            ,AO_CODE = @AO_CODE
                            ,CIF_NUMBER = @CIF_NUMBER
                            ,FULL_NAME = @FULL_NAME
                            ,ALIAS_NAME = @ALIAS_NAME
                            ,SHORT_NAME = @SHORT_NAME
                            ,GENDER = @GENDER
                            ,FIRST_NAME = @FIRST_NAME
                            ,MIDLE_NAME = @MIDLE_NAME
                            ,LAST_NAME = @LAST_NAME
                            ,MOTHER_NAME = @MOTHER_NAME
                            ,MARITAL_STATUS = @MARITAL_STATUS
                            ,RESIDENCE_PHONE = @RESIDENCE_PHONE
                            ,MOBILE_PHONE = @MOBILE_PHONE
                            ,PLACE_OF_BIRTH = @PLACE_OF_BIRTH
                            ,DATE_OF_BIRTH = @DATE_OF_BIRTH
                            ,HOME_STATUS = @HOME_STATUS
                            ,CUSTOMER_IC = @CUSTOMER_IC
                            ,IC_EXPIRY_DATE = @IC_EXPIRY_DATE
                            ,MONTHLY_INCOME = @MONTHLY_INCOME
                            ,NUMBER_OF_DEPENDENTS = @NUMBER_OF_DEPENDENTS
                            ,MAIL_ADDRESS_1 = @MAIL_ADDRESS_1
                            ,MAIL_ADDRESS_2 = @MAIL_ADDRESS_2
                            ,MAIL_ADDRESS_3 = @MAIL_ADDRESS_3
                            ,MAIL_ZIP_CODE = @MAIL_ZIP_CODE
                            ,PERMANENT_ADDRESS_1 = @PERMANENT_ADDRESS_1
                            ,PERMANENT_ADDRESS_2 = @PERMANENT_ADDRESS_2
                            ,PERMANENT_ADDRESS_3 = @PERMANENT_ADDRESS_3
                            ,PERMANENT_ZIP_CODE = @PERMANENT_ZIP_CODE
                            ,HOLD_MAIL_ADDRESS_1 = @HOLD_MAIL_ADDRESS_1
                            ,HOLD_MAIL_ADDRESS_2 = @HOLD_MAIL_ADDRESS_2
                            ,HOLD_MAIL_ADDRESS_3 = @HOLD_MAIL_ADDRESS_3
                            ,HOLD_MAIL_ZIP_CODE = @HOLD_MAIL_ZIP_CODE
                            ,IS_REQUEST = @IS_REQUEST
                            ,PROGRESS_STATUS = @PROGRESS_STATUS
                            ,REFFERAL = @REFFERAL
                            ,APPROVAL = @APPROVAL
                            ,MAINTAIN = @MAINTAIN
                            ,ENTRY_BY = @ENTRY_BY
                            ,ENTRY_DATE = @ENTRY_DATE
                            ,UPDATE_BY = @UPDATE_BY
                            ,UPDATE_DATE = @UPDATE_DATE
                            ,UPLOAD_BY = @UPLOAD_BY
                            ,UPLOAD_DATE = @UPLOAD_DATE
                            ,IS_UPLOAD = @IS_UPLOAD
                            WHERE (PROSPECT_CIF_ID = @PROSPECT_CIF_ID)
                        IF @@ROWCOUNT = 0
                        INSERT INTO TBL_PROSPECT_CIF(PROSPECT_CIF_ID
                            ,LOB
                            ,APPLICATION_ID
                            ,UNIT_CODE
                            ,AO_CODE
                            ,CIF_NUMBER
                            ,FULL_NAME
                            ,ALIAS_NAME
                            ,SHORT_NAME
                            ,GENDER
                            ,FIRST_NAME
                            ,MIDLE_NAME
                            ,LAST_NAME
                            ,MOTHER_NAME
                            ,MARITAL_STATUS
                            ,RESIDENCE_PHONE
                            ,MOBILE_PHONE
                            ,PLACE_OF_BIRTH
                            ,DATE_OF_BIRTH
                            ,HOME_STATUS
                            ,CUSTOMER_IC
                            ,IC_EXPIRY_DATE
                            ,MONTHLY_INCOME
                            ,NUMBER_OF_DEPENDENTS
                            ,MAIL_ADDRESS_1
                            ,MAIL_ADDRESS_2
                            ,MAIL_ADDRESS_3
                            ,MAIL_ZIP_CODE
                            ,PERMANENT_ADDRESS_1
                            ,PERMANENT_ADDRESS_2
                            ,PERMANENT_ADDRESS_3
                            ,PERMANENT_ZIP_CODE
                            ,HOLD_MAIL_ADDRESS_1
                            ,HOLD_MAIL_ADDRESS_2
                            ,HOLD_MAIL_ADDRESS_3
                            ,HOLD_MAIL_ZIP_CODE
                            ,IS_REQUEST
                            ,PROGRESS_STATUS
                            ,REFFERAL
                            ,APPROVAL
                            ,MAINTAIN
                            ,ENTRY_BY
                            ,ENTRY_DATE
                            ,UPDATE_BY
                            ,UPDATE_DATE
                            ,UPLOAD_BY
                            ,UPLOAD_DATE
                            ,IS_UPLOAD
                            ) 
                            VALUES(@PROSPECT_CIF_ID
                            ,@LOB
                            ,@APPLICATION_ID
                            ,@UNIT_CODE
                            ,@AO_CODE
                            ,@CIF_NUMBER
                            ,@FULL_NAME
                            ,@ALIAS_NAME
                            ,@SHORT_NAME
                            ,@GENDER
                            ,@FIRST_NAME
                            ,@MIDLE_NAME
                            ,@LAST_NAME
                            ,@MOTHER_NAME
                            ,@MARITAL_STATUS
                            ,@RESIDENCE_PHONE
                            ,@MOBILE_PHONE
                            ,@PLACE_OF_BIRTH
                            ,@DATE_OF_BIRTH
                            ,@HOME_STATUS
                            ,@CUSTOMER_IC
                            ,@IC_EXPIRY_DATE
                            ,@MONTHLY_INCOME
                            ,@NUMBER_OF_DEPENDENTS
                            ,@MAIL_ADDRESS_1
                            ,@MAIL_ADDRESS_2
                            ,@MAIL_ADDRESS_3
                            ,@MAIL_ZIP_CODE
                            ,@PERMANENT_ADDRESS_1
                            ,@PERMANENT_ADDRESS_2
                            ,@PERMANENT_ADDRESS_3
                            ,@PERMANENT_ZIP_CODE
                            ,@HOLD_MAIL_ADDRESS_1
                            ,@HOLD_MAIL_ADDRESS_2
                            ,@HOLD_MAIL_ADDRESS_3
                            ,@HOLD_MAIL_ZIP_CODE
                            ,@IS_REQUEST
                            ,@PROGRESS_STATUS
                            ,@REFFERAL
                            ,@APPROVAL
                            ,@MAINTAIN
                            ,@ENTRY_BY
                            ,@ENTRY_DATE
                            ,@UPDATE_BY
                            ,@UPDATE_DATE
                            ,@UPLOAD_BY
                            ,@UPLOAD_DATE
                            ,@IS_UPLOAD
                            );");

                    db.AddInParameter(cmd, "@PROSPECT_CIF_ID", DbType.String, Convert.ToString(dr["PROSPECT_CIF_ID"]));
                    db.AddInParameter(cmd, "@LOB", DbType.String, Convert.ToString(dr["LOB"]));
                    db.AddInParameter(cmd, "@APPLICATION_ID", DbType.String, Convert.ToString(dr["APPLICATION_ID"]));
                    db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                    db.AddInParameter(cmd, "@AO_CODE", DbType.String, Convert.ToString(dr["AO_CODE"]));
                    db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int64, Convert.ToInt64(dr["CIF_NUMBER"]));
                    db.AddInParameter(cmd, "@FULL_NAME", DbType.String, Convert.ToString(dr["FULL_NAME"]));
                    db.AddInParameter(cmd, "@ALIAS_NAME", DbType.String, Convert.ToString(dr["ALIAS_NAME"]));
                    db.AddInParameter(cmd, "@SHORT_NAME", DbType.String, Convert.ToString(dr["SHORT_NAME"]));
                    db.AddInParameter(cmd, "@GENDER", DbType.String, Convert.ToString(dr["GENDER"]));
                    db.AddInParameter(cmd, "@FIRST_NAME", DbType.String, Convert.ToString(dr["FIRST_NAME"]));
                    db.AddInParameter(cmd, "@MIDLE_NAME", DbType.String, Convert.ToString(dr["MIDLE_NAME"]));
                    db.AddInParameter(cmd, "@LAST_NAME", DbType.String, Convert.ToString(dr["LAST_NAME"]));
                    db.AddInParameter(cmd, "@MOTHER_NAME", DbType.String, Convert.ToString(dr["MOTHER_NAME"]));
                    db.AddInParameter(cmd, "@MARITAL_STATUS", DbType.String, Convert.ToString(dr["MARITAL_STATUS"]));
                    db.AddInParameter(cmd, "@RESIDENCE_PHONE", DbType.String, Convert.ToString(dr["RESIDENCE_PHONE"]));
                    db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, Convert.ToString(dr["MOBILE_PHONE"]));
                    db.AddInParameter(cmd, "@PLACE_OF_BIRTH", DbType.String, Convert.ToString(dr["PLACE_OF_BIRTH"]));
                    db.AddInParameter(cmd, "@DATE_OF_BIRTH", DbType.DateTime, Convert.ToDateTime(dr["DATE_OF_BIRTH"]));
                    db.AddInParameter(cmd, "@HOME_STATUS", DbType.String, Convert.ToString(dr["HOME_STATUS"]));
                    db.AddInParameter(cmd, "@CUSTOMER_IC", DbType.String, Convert.ToString(dr["CUSTOMER_IC"]));
                    db.AddInParameter(cmd, "@IC_EXPIRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["IC_EXPIRY_DATE"]));
                    db.AddInParameter(cmd, "@MONTHLY_INCOME", DbType.Double, Convert.ToDouble(dr["MONTHLY_INCOME"]));
                    db.AddInParameter(cmd, "@NUMBER_OF_DEPENDENTS", DbType.Int16, Convert.ToInt16(dr["NUMBER_OF_DEPENDENTS"]));
                    db.AddInParameter(cmd, "@MAIL_ADDRESS_1", DbType.String, Convert.ToString(dr["MAIL_ADDRESS_1"]));
                    db.AddInParameter(cmd, "@MAIL_ADDRESS_2", DbType.String, Convert.ToString(dr["MAIL_ADDRESS_2"]));
                    db.AddInParameter(cmd, "@MAIL_ADDRESS_3", DbType.String, Convert.ToString(dr["MAIL_ADDRESS_3"]));
                    db.AddInParameter(cmd, "@MAIL_ZIP_CODE", DbType.String, Convert.ToString(dr["MAIL_ZIP_CODE"]));
                    db.AddInParameter(cmd, "@PERMANENT_ADDRESS_1", DbType.String, Convert.ToString(dr["PERMANENT_ADDRESS_1"]));
                    db.AddInParameter(cmd, "@PERMANENT_ADDRESS_2", DbType.String, Convert.ToString(dr["PERMANENT_ADDRESS_2"]));
                    db.AddInParameter(cmd, "@PERMANENT_ADDRESS_3", DbType.String, Convert.ToString(dr["PERMANENT_ADDRESS_3"]));
                    db.AddInParameter(cmd, "@PERMANENT_ZIP_CODE", DbType.String, Convert.ToString(dr["PERMANENT_ZIP_CODE"]));
                    db.AddInParameter(cmd, "@HOLD_MAIL_ADDRESS_1", DbType.String, Convert.ToString(dr["HOLD_MAIL_ADDRESS_1"]));
                    db.AddInParameter(cmd, "@HOLD_MAIL_ADDRESS_2", DbType.String, Convert.ToString(dr["HOLD_MAIL_ADDRESS_2"]));
                    db.AddInParameter(cmd, "@HOLD_MAIL_ADDRESS_3", DbType.String, Convert.ToString(dr["HOLD_MAIL_ADDRESS_3"]));
                    db.AddInParameter(cmd, "@HOLD_MAIL_ZIP_CODE", DbType.String, Convert.ToString(dr["HOLD_MAIL_ZIP_CODE"]));
                    db.AddInParameter(cmd, "@IS_REQUEST", DbType.String, Convert.ToString(dr["IS_REQUEST"]));
                    db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, Convert.ToString(dr["PROGRESS_STATUS"]));
                    db.AddInParameter(cmd, "@REFFERAL", DbType.String, Convert.ToString(dr["REFFERAL"]));
                    db.AddInParameter(cmd, "@APPROVAL", DbType.String, Convert.ToString(dr["APPROVAL"]));
                    db.AddInParameter(cmd, "@MAINTAIN", DbType.String, Convert.ToString(dr["MAINTAIN"]));
                    db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                    db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                    db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                    db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                    db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, Convert.ToString(dr["UPLOAD_BY"]));
                    db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPLOAD_DATE"]));
                    db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");

                    db.ExecuteNonQuery(cmd);
                    return "0";
                }
            }
            return "~";
        }

        [WebMethod]
        public string RequestCasa(string nip, string pass, byte[] data)
        {
            if (IsValidUser(nip, pass))
            {
                DataSet MyDataSet = TransformIntoDataSet(data);
                if (ValidateDataSetEmpty(MyDataSet))
                {
                    Database db = DBConn.CreateDatabase();
                    DataRow dr = MyDataSet.Tables[0].Rows[0];

                    DbCommand cmd = db.GetSqlStringCommand(@"SELECT COUNT(0) FROM TBL_PROSPECT_CASA
                    WHERE JOB_STATUS = 'RUN' AND PROSPECT_CASA_ID = @PROSPECT_CASA_ID");

                    db.AddInParameter(cmd, "@PROSPECT_CASA_ID", DbType.String, Convert.ToString(dr["PROSPECT_CASA_ID"]));

                    object result = db.ExecuteScalar(cmd);
                    if (Convert.ToInt32(result) > 0) return "1";

                    cmd = db.GetSqlStringCommand(@"
                        UPDATE TBL_PROSPECT_CASA SET 
                             LOB = @LOB
                            ,APPLICATION_ID = @APPLICATION_ID
                            ,REGULAR_LUCKY_FLAG = @REGULAR_LUCKY_FLAG
                            ,UNIT_CODE = @UNIT_CODE
                            ,ACCESS_CODE = @ACCESS_CODE
                            ,PRODUCT_CODE = @PRODUCT_CODE
                            ,CIF_NUMBER = @CIF_NUMBER
                            ,ACCOUNT_NUMBER = @ACCOUNT_NUMBER
                            ,ACCOUNT_OPENING_DATE = @ACCOUNT_OPENING_DATE
                            ,ADDITIONAL_REMARKS = @ADDITIONAL_REMARKS
                            ,AO_BUSINESS = @AO_BUSINESS
                            ,AO_OPERATION = @AO_OPERATION
                            ,CURRENT_ACCOUNT_BI_CODE = @CURRENT_ACCOUNT_BI_CODE
                            ,SAVING_ACCOUNT_BI_CODE = @SAVING_ACCOUNT_BI_CODE
                            ,OTHER_ACCOUNT_BI_CODE = @OTHER_ACCOUNT_BI_CODE
                            ,TD_ACCOUNT_BI_CODE = @TD_ACCOUNT_BI_CODE
                            ,ADDITIONAL_FIELD_1 = @ADDITIONAL_FIELD_1
                            ,ADDITIONAL_FIELD_2 = @ADDITIONAL_FIELD_2
                            ,ADDITIONAL_FIELD_3 = @ADDITIONAL_FIELD_3
                            ,ADDITIONAL_FIELD_4 = @ADDITIONAL_FIELD_4
                            ,ADDITIONAL_FIELD_5 = @ADDITIONAL_FIELD_5
                            ,ADDITIONAL_FIELD_6 = @ADDITIONAL_FIELD_6
                            ,ADDITIONAL_FIELD_7 = @ADDITIONAL_FIELD_7
                            ,ADDITIONAL_FIELD_8 = @ADDITIONAL_FIELD_8
                            ,ADDITIONAL_FIELD_9 = @ADDITIONAL_FIELD_9
                            ,ADDITIONAL_FIELD_10 = @ADDITIONAL_FIELD_10
                            ,IS_REQUEST = @IS_REQUEST
                            ,PROGRESS_STATUS = @PROGRESS_STATUS
                            ,REFFERAL = @REFFERAL
                            ,APPROVAL = @APPROVAL
                            ,MAINTAIN = @MAINTAIN
                            ,ENTRY_BY = @ENTRY_BY
                            ,ENTRY_DATE = @ENTRY_DATE
                            ,UPDATE_BY = @UPDATE_BY
                            ,UPDATE_DATE = @UPDATE_DATE
                            ,UPLOAD_BY = @UPLOAD_BY
                            ,UPLOAD_DATE = @UPLOAD_DATE
                            ,IS_UPLOAD = @IS_UPLOAD
                            WHERE (PROSPECT_CASA_ID = @PROSPECT_CASA_ID)
                         IF @@ROWCOUNT = 0
                         INSERT INTO TBL_PROSPECT_CASA(
                            PROSPECT_CASA_ID
                            ,LOB
                            ,APPLICATION_ID
                            ,REGULAR_LUCKY_FLAG
                            ,UNIT_CODE
                            ,ACCESS_CODE
                            ,PRODUCT_CODE
                            ,CIF_NUMBER
                            ,ACCOUNT_NUMBER
                            ,ACCOUNT_OPENING_DATE
                            ,ADDITIONAL_REMARKS
                            ,AO_BUSINESS
                            ,AO_OPERATION
                            ,CURRENT_ACCOUNT_BI_CODE
                            ,SAVING_ACCOUNT_BI_CODE
                            ,OTHER_ACCOUNT_BI_CODE
                            ,TD_ACCOUNT_BI_CODE
                            ,ADDITIONAL_FIELD_1
                            ,ADDITIONAL_FIELD_2
                            ,ADDITIONAL_FIELD_3
                            ,ADDITIONAL_FIELD_4
                            ,ADDITIONAL_FIELD_5
                            ,ADDITIONAL_FIELD_6
                            ,ADDITIONAL_FIELD_7
                            ,ADDITIONAL_FIELD_8
                            ,ADDITIONAL_FIELD_9
                            ,ADDITIONAL_FIELD_10
                            ,IS_REQUEST
                            ,PROGRESS_STATUS
                            ,REFFERAL
                            ,APPROVAL
                            ,MAINTAIN
                            ,ENTRY_BY
                            ,ENTRY_DATE
                            ,UPDATE_BY
                            ,UPDATE_DATE
                            ,UPLOAD_BY
                            ,UPLOAD_DATE
                            ,IS_UPLOAD
                            ) 
                            VALUES(@PROSPECT_CASA_ID
                            ,@LOB
                            ,@APPLICATION_ID
                            ,@REGULAR_LUCKY_FLAG
                            ,@UNIT_CODE
                            ,@ACCESS_CODE
                            ,@PRODUCT_CODE
                            ,@CIF_NUMBER
                            ,@ACCOUNT_NUMBER
                            ,@ACCOUNT_OPENING_DATE
                            ,@ADDITIONAL_REMARKS
                            ,@AO_BUSINESS
                            ,@AO_OPERATION
                            ,@CURRENT_ACCOUNT_BI_CODE
                            ,@SAVING_ACCOUNT_BI_CODE
                            ,@OTHER_ACCOUNT_BI_CODE
                            ,@TD_ACCOUNT_BI_CODE
                            ,@ADDITIONAL_FIELD_1
                            ,@ADDITIONAL_FIELD_2
                            ,@ADDITIONAL_FIELD_3
                            ,@ADDITIONAL_FIELD_4
                            ,@ADDITIONAL_FIELD_5
                            ,@ADDITIONAL_FIELD_6
                            ,@ADDITIONAL_FIELD_7
                            ,@ADDITIONAL_FIELD_8
                            ,@ADDITIONAL_FIELD_9
                            ,@ADDITIONAL_FIELD_10
                            ,@IS_REQUEST
                            ,@PROGRESS_STATUS
                            ,@REFFERAL
                            ,@APPROVAL
                            ,@MAINTAIN
                            ,@ENTRY_BY
                            ,@ENTRY_DATE
                            ,@UPDATE_BY
                            ,@UPDATE_DATE
                            ,@UPLOAD_BY
                            ,@UPLOAD_DATE
                            ,@IS_UPLOAD
                            );");

                    db.AddInParameter(cmd, "@PROSPECT_CASA_ID", DbType.String, Convert.ToString(dr["PROSPECT_CASA_ID"]));
                    db.AddInParameter(cmd, "@LOB", DbType.String, Convert.ToString(dr["LOB"]));
                    db.AddInParameter(cmd, "@APPLICATION_ID", DbType.String, Convert.ToString(dr["APPLICATION_ID"]));
                    db.AddInParameter(cmd, "@REGULAR_LUCKY_FLAG", DbType.String, Convert.ToString(dr["REGULAR_LUCKY_FLAG"]));
                    db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                    db.AddInParameter(cmd, "@ACCESS_CODE", DbType.Int16, Convert.ToInt16(dr["ACCESS_CODE"]));
                    db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.Int16, Convert.ToInt16(dr["PRODUCT_CODE"]));
                    db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int64, Convert.ToInt64(dr["CIF_NUMBER"]));
                    db.AddInParameter(cmd, "@ACCOUNT_NUMBER", DbType.String, Convert.ToString(dr["ACCOUNT_NUMBER"]));
                    db.AddInParameter(cmd, "@ACCOUNT_OPENING_DATE", DbType.DateTime, Convert.ToDateTime(dr["ACCOUNT_OPENING_DATE"]));
                    db.AddInParameter(cmd, "@ADDITIONAL_REMARKS", DbType.String, Convert.ToString(dr["ADDITIONAL_REMARKS"]));
                    db.AddInParameter(cmd, "@AO_BUSINESS", DbType.String, Convert.ToString(dr["AO_BUSINESS"]));
                    db.AddInParameter(cmd, "@AO_OPERATION", DbType.String, Convert.ToString(dr["AO_OPERATION"]));
                    db.AddInParameter(cmd, "@CURRENT_ACCOUNT_BI_CODE", DbType.String, Convert.ToString(dr["CURRENT_ACCOUNT_BI_CODE"]));
                    db.AddInParameter(cmd, "@SAVING_ACCOUNT_BI_CODE", DbType.String, Convert.ToString(dr["SAVING_ACCOUNT_BI_CODE"]));
                    db.AddInParameter(cmd, "@OTHER_ACCOUNT_BI_CODE", DbType.String, Convert.ToString(dr["OTHER_ACCOUNT_BI_CODE"]));
                    db.AddInParameter(cmd, "@TD_ACCOUNT_BI_CODE", DbType.String, Convert.ToString(dr["TD_ACCOUNT_BI_CODE"]));
                    db.AddInParameter(cmd, "@ADDITIONAL_FIELD_1", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_1"]));
                    db.AddInParameter(cmd, "@ADDITIONAL_FIELD_2", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_2"]));
                    db.AddInParameter(cmd, "@ADDITIONAL_FIELD_3", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_3"]));
                    db.AddInParameter(cmd, "@ADDITIONAL_FIELD_4", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_4"]));
                    db.AddInParameter(cmd, "@ADDITIONAL_FIELD_5", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_5"]));
                    db.AddInParameter(cmd, "@ADDITIONAL_FIELD_6", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_6"]));
                    db.AddInParameter(cmd, "@ADDITIONAL_FIELD_7", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_7"]));
                    db.AddInParameter(cmd, "@ADDITIONAL_FIELD_8", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_8"]));
                    db.AddInParameter(cmd, "@ADDITIONAL_FIELD_9", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_9"]));
                    db.AddInParameter(cmd, "@ADDITIONAL_FIELD_10", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_10"]));
                    db.AddInParameter(cmd, "@IS_REQUEST", DbType.String, Convert.ToString(dr["IS_REQUEST"]));
                    db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, Convert.ToString(dr["PROGRESS_STATUS"]));
                    db.AddInParameter(cmd, "@REFFERAL", DbType.String, Convert.ToString(dr["REFFERAL"]));
                    db.AddInParameter(cmd, "@APPROVAL", DbType.String, Convert.ToString(dr["APPROVAL"]));
                    db.AddInParameter(cmd, "@MAINTAIN", DbType.String, Convert.ToString(dr["MAINTAIN"]));
                    db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                    db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToString(dr["ENTRY_DATE"]));
                    db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                    db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                    db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, Convert.ToString(dr["UPLOAD_BY"]));
                    db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPLOAD_DATE"]));
                    db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");

                    db.ExecuteNonQuery(cmd);
                    return "0";
                }
            }
            return "~";
        }

        [WebMethod]
        public string UploadCASA(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"                      
                        UPDATE TBL_PROSPECT_CASA SET 
                             LOB = @LOB
                            ,APPLICATION_ID = @APPLICATION_ID
                            ,REGULAR_LUCKY_FLAG = @REGULAR_LUCKY_FLAG
                            ,UNIT_CODE = @UNIT_CODE
                            ,ACCESS_CODE = @ACCESS_CODE
                            ,PRODUCT_CODE = @PRODUCT_CODE
                            ,CIF_NUMBER = @CIF_NUMBER
                            ,ACCOUNT_NUMBER = @ACCOUNT_NUMBER
                            ,ACCOUNT_OPENING_DATE = @ACCOUNT_OPENING_DATE
                            ,ADDITIONAL_REMARKS = @ADDITIONAL_REMARKS
                            ,AO_BUSINESS = @AO_BUSINESS
                            ,AO_OPERATION = @AO_OPERATION
                            ,CURRENT_ACCOUNT_BI_CODE = @CURRENT_ACCOUNT_BI_CODE
                            ,SAVING_ACCOUNT_BI_CODE = @SAVING_ACCOUNT_BI_CODE
                            ,OTHER_ACCOUNT_BI_CODE = @OTHER_ACCOUNT_BI_CODE
                            ,TD_ACCOUNT_BI_CODE = @TD_ACCOUNT_BI_CODE
                            ,ADDITIONAL_FIELD_1 = @ADDITIONAL_FIELD_1
                            ,ADDITIONAL_FIELD_2 = @ADDITIONAL_FIELD_2
                            ,ADDITIONAL_FIELD_3 = @ADDITIONAL_FIELD_3
                            ,ADDITIONAL_FIELD_4 = @ADDITIONAL_FIELD_4
                            ,ADDITIONAL_FIELD_5 = @ADDITIONAL_FIELD_5
                            ,ADDITIONAL_FIELD_6 = @ADDITIONAL_FIELD_6
                            ,ADDITIONAL_FIELD_7 = @ADDITIONAL_FIELD_7
                            ,ADDITIONAL_FIELD_8 = @ADDITIONAL_FIELD_8
                            ,ADDITIONAL_FIELD_9 = @ADDITIONAL_FIELD_9
                            ,ADDITIONAL_FIELD_10 = @ADDITIONAL_FIELD_10
                            ,IS_REQUEST = @IS_REQUEST
                            ,PROGRESS_STATUS = @PROGRESS_STATUS
                            ,REFFERAL = @REFFERAL
                            ,APPROVAL = @APPROVAL
                            ,MAINTAIN = @MAINTAIN
                            ,ENTRY_BY = @ENTRY_BY
                            ,ENTRY_DATE = @ENTRY_DATE
                            ,UPDATE_BY = @UPDATE_BY
                            ,UPDATE_DATE = @UPDATE_DATE
                            ,UPLOAD_BY = @UPLOAD_BY
                            ,UPLOAD_DATE = @UPLOAD_DATE
                            ,IS_UPLOAD = @IS_UPLOAD

                            ,PROSPECT_TYPE = @PROSPECT_TYPE
                            ,REFERENSI = @REFERENSI
                            ,DEBTOR_TYPE = @DEBTOR_TYPE
                            ,EKSTERNAL = @EKSTERNAL
                            ,MOBILE_PHONE = @MOBILE_PHONE
                            ,RESIDENTIAL_PHONE = @RESIDENTIAL_PHONE
                            ,CHANNELING = @CHANNELING
                            ,SETORAN_AWAL = @SETORAN_AWAL
                            ,TD_ACCOUNT_NUMBER = @TD_ACCOUNT_NUMBER

                           ,FULL_NAME = @FULL_NAME
                           ,ASSIGN_TO = @ASSIGN_TO

                            ,GU_FUP = @GU_FUP
                            ,GU_CLOSE_DEAL = @GU_CLOSE_DEAL

                            WHERE (PROSPECT_CASA_ID = @PROSPECT_CASA_ID)
                         IF @@ROWCOUNT = 0
                         INSERT INTO TBL_PROSPECT_CASA(
                            PROSPECT_CASA_ID
                            ,LOB
                            ,APPLICATION_ID
                            ,REGULAR_LUCKY_FLAG
                            ,UNIT_CODE
                            ,ACCESS_CODE
                            ,PRODUCT_CODE
                            ,CIF_NUMBER
                            ,ACCOUNT_NUMBER
                            ,ACCOUNT_OPENING_DATE
                            ,ADDITIONAL_REMARKS
                            ,AO_BUSINESS
                            ,AO_OPERATION
                            ,CURRENT_ACCOUNT_BI_CODE
                            ,SAVING_ACCOUNT_BI_CODE
                            ,OTHER_ACCOUNT_BI_CODE
                            ,TD_ACCOUNT_BI_CODE
                            ,ADDITIONAL_FIELD_1
                            ,ADDITIONAL_FIELD_2
                            ,ADDITIONAL_FIELD_3
                            ,ADDITIONAL_FIELD_4
                            ,ADDITIONAL_FIELD_5
                            ,ADDITIONAL_FIELD_6
                            ,ADDITIONAL_FIELD_7
                            ,ADDITIONAL_FIELD_8
                            ,ADDITIONAL_FIELD_9
                            ,ADDITIONAL_FIELD_10
                            ,IS_REQUEST
                            ,PROGRESS_STATUS
                            ,REFFERAL
                            ,APPROVAL
                            ,MAINTAIN
                            ,ENTRY_BY
                            ,ENTRY_DATE
                            ,UPDATE_BY
                            ,UPDATE_DATE
                            ,UPLOAD_BY
                            ,UPLOAD_DATE
                            ,IS_UPLOAD

                            ,PROSPECT_TYPE
                            ,REFERENSI
                            ,DEBTOR_TYPE
                            ,EKSTERNAL
                            ,MOBILE_PHONE
                            ,RESIDENTIAL_PHONE
                            ,CHANNELING
                            ,SETORAN_AWAL
                            ,TD_ACCOUNT_NUMBER

                            ,FULL_NAME
                            ,ASSIGN_TO

                            ,GU_FUP
                            ,GU_CLOSE_DEAL
                            ) 
                            VALUES(@PROSPECT_CASA_ID
                            ,@LOB
                            ,@APPLICATION_ID
                            ,@REGULAR_LUCKY_FLAG
                            ,@UNIT_CODE
                            ,@ACCESS_CODE
                            ,@PRODUCT_CODE
                            ,@CIF_NUMBER
                            ,@ACCOUNT_NUMBER
                            ,@ACCOUNT_OPENING_DATE
                            ,@ADDITIONAL_REMARKS
                            ,@AO_BUSINESS
                            ,@AO_OPERATION
                            ,@CURRENT_ACCOUNT_BI_CODE
                            ,@SAVING_ACCOUNT_BI_CODE
                            ,@OTHER_ACCOUNT_BI_CODE
                            ,@TD_ACCOUNT_BI_CODE
                            ,@ADDITIONAL_FIELD_1
                            ,@ADDITIONAL_FIELD_2
                            ,@ADDITIONAL_FIELD_3
                            ,@ADDITIONAL_FIELD_4
                            ,@ADDITIONAL_FIELD_5
                            ,@ADDITIONAL_FIELD_6
                            ,@ADDITIONAL_FIELD_7
                            ,@ADDITIONAL_FIELD_8
                            ,@ADDITIONAL_FIELD_9
                            ,@ADDITIONAL_FIELD_10
                            ,@IS_REQUEST
                            ,@PROGRESS_STATUS
                            ,@REFFERAL
                            ,@APPROVAL
                            ,@MAINTAIN
                            ,@ENTRY_BY
                            ,@ENTRY_DATE
                            ,@UPDATE_BY
                            ,@UPDATE_DATE
                            ,@UPLOAD_BY
                            ,@UPLOAD_DATE
                            ,@IS_UPLOAD

                            ,@PROSPECT_TYPE
                            ,@REFERENSI
                            ,@DEBTOR_TYPE
                            ,@EKSTERNAL
                            ,@MOBILE_PHONE
                            ,@RESIDENTIAL_PHONE
                            ,@CHANNELING
                            ,@SETORAN_AWAL
                            ,@TD_ACCOUNT_NUMBER

                            ,@FULL_NAME
                            ,@ASSIGN_TO

                            ,@GU_FUP
                            ,@GU_CLOSE_DEAL
                            );


                        -- Jika nomor CIF belum ada
                        UPDATE CASA SET CASA.APPLICATION_ID = CIF.APPLICATION_ID
                        FROM    TBL_PROSPECT_CASA CASA
                        INNER   JOIN TBL_PROSPECT_CIF CIF ON CASA.PROSPECT_CASA_ID = CIF.PROSPECT_CIF_ID
                        WHERE	CASA.APPLICATION_ID = 'N/A'
                        
                        ");

                        //                        -- Jika nomor CIF ada
                        //UPDATE CASA SET CASA.APPLICATION_ID = CIF.APPLICATION_ID
                        //FROM    TBL_PROSPECT_CASA CASA
                        //INNER   JOIN TBL_PROSPECT_CIF CIF ON CASA.PROSPECT_CASA_ID = CIF.PROSPECT_CIF_ID
                        //WHERE   CIF.APPLICATION_ID IS NOT NULL AND CIF.APPLICATION_ID <> '' AND CIF.APPLICATION_ID <> '0'



                        db.AddInParameter(cmd, "@PROSPECT_CASA_ID", DbType.String, Convert.ToString(dr["PROSPECT_CASA_ID"]));
                        db.AddInParameter(cmd, "@LOB", DbType.String, Convert.ToString(dr["LOB"]));
                        db.AddInParameter(cmd, "@APPLICATION_ID", DbType.String, Convert.ToString(dr["APPLICATION_ID"]));
                        db.AddInParameter(cmd, "@REGULAR_LUCKY_FLAG", DbType.String, Convert.ToString(dr["REGULAR_LUCKY_FLAG"]));
                        db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                        db.AddInParameter(cmd, "@ACCESS_CODE", DbType.Int16, Convert.ToInt16(dr["ACCESS_CODE"]));
                        db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.Int16, Convert.ToInt16(dr["PRODUCT_CODE"]));
                        db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int64, Convert.ToInt64(dr["CIF_NUMBER"]));
                        db.AddInParameter(cmd, "@ACCOUNT_NUMBER", DbType.String, Convert.ToString(dr["ACCOUNT_NUMBER"]));
                        db.AddInParameter(cmd, "@ACCOUNT_OPENING_DATE", DbType.DateTime, Convert.ToDateTime(dr["ACCOUNT_OPENING_DATE"]));
                        db.AddInParameter(cmd, "@ADDITIONAL_REMARKS", DbType.String, Convert.ToString(dr["ADDITIONAL_REMARKS"]));
                        db.AddInParameter(cmd, "@AO_BUSINESS", DbType.String, Convert.ToString(dr["AO_BUSINESS"]));
                        db.AddInParameter(cmd, "@AO_OPERATION", DbType.String, Convert.ToString(dr["AO_OPERATION"]));
                        db.AddInParameter(cmd, "@CURRENT_ACCOUNT_BI_CODE", DbType.String, Convert.ToString(dr["CURRENT_ACCOUNT_BI_CODE"]));
                        db.AddInParameter(cmd, "@SAVING_ACCOUNT_BI_CODE", DbType.String, Convert.ToString(dr["SAVING_ACCOUNT_BI_CODE"]));
                        db.AddInParameter(cmd, "@OTHER_ACCOUNT_BI_CODE", DbType.String, Convert.ToString(dr["OTHER_ACCOUNT_BI_CODE"]));
                        db.AddInParameter(cmd, "@TD_ACCOUNT_BI_CODE", DbType.String, Convert.ToString(dr["TD_ACCOUNT_BI_CODE"]));
                        db.AddInParameter(cmd, "@ADDITIONAL_FIELD_1", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_1"]));
                        db.AddInParameter(cmd, "@ADDITIONAL_FIELD_2", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_2"]));
                        db.AddInParameter(cmd, "@ADDITIONAL_FIELD_3", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_3"]));
                        db.AddInParameter(cmd, "@ADDITIONAL_FIELD_4", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_4"]));
                        db.AddInParameter(cmd, "@ADDITIONAL_FIELD_5", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_5"]));
                        db.AddInParameter(cmd, "@ADDITIONAL_FIELD_6", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_6"]));
                        db.AddInParameter(cmd, "@ADDITIONAL_FIELD_7", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_7"]));
                        db.AddInParameter(cmd, "@ADDITIONAL_FIELD_8", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_8"]));
                        db.AddInParameter(cmd, "@ADDITIONAL_FIELD_9", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_9"]));
                        db.AddInParameter(cmd, "@ADDITIONAL_FIELD_10", DbType.String, Convert.ToString(dr["ADDITIONAL_FIELD_10"]));
                        db.AddInParameter(cmd, "@IS_REQUEST", DbType.String, Convert.ToString(dr["IS_REQUEST"]));
                        db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, Convert.ToString(dr["PROGRESS_STATUS"]));
                        db.AddInParameter(cmd, "@REFFERAL", DbType.String, Convert.ToString(dr["REFFERAL"]));
                        db.AddInParameter(cmd, "@APPROVAL", DbType.String, Convert.ToString(dr["APPROVAL"]));
                        db.AddInParameter(cmd, "@MAINTAIN", DbType.String, Convert.ToString(dr["MAINTAIN"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToString(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, Convert.ToString(dr["UPLOAD_BY"]));
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPLOAD_DATE"]));
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");
                        db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, Convert.ToString(dr["PROSPECT_TYPE"]));
                        db.AddInParameter(cmd, "@REFERENSI", DbType.String, Convert.ToString(dr["REFERENSI"]));
                        db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, Convert.ToString(dr["DEBTOR_TYPE"]));
                        db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, Convert.ToString(dr["EKSTERNAL"]));
                        db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, Convert.ToString(dr["MOBILE_PHONE"]));
                        db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, Convert.ToString(dr["RESIDENTIAL_PHONE"]));
                        db.AddInParameter(cmd, "@CHANNELING", DbType.String, Convert.ToString(dr["CHANNELING"]));
                        db.AddInParameter(cmd, "@SETORAN_AWAL", DbType.Double, Convert.ToDouble(dr["SETORAN_AWAL"]));
                        db.AddInParameter(cmd, "@TD_ACCOUNT_NUMBER", DbType.String, Convert.ToString(dr["TD_ACCOUNT_NUMBER"]));
                        db.AddInParameter(cmd, "@FULL_NAME", DbType.String, Convert.ToString(dr["FULL_NAME"]));
                        db.AddInParameter(cmd, "@ASSIGN_TO", DbType.String, Convert.ToString(dr["ASSIGN_TO"]));

                        db.AddInParameter(cmd, "@GU_FUP", DbType.DateTime, Convert.ToString(dr["GU_FUP"]));
                        db.AddInParameter(cmd, "@GU_CLOSE_DEAL", DbType.DateTime, Convert.ToString(dr["GU_CLOSE_DEAL"]));

                        if (db.ExecuteNonQuery(cmd) > 0)
                        {
                            result++;
                        }
                    }
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public byte[] GetCIFByUnit(string nip, string pass, string unit, string mac_address, DateTime job_date)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetCIFByUnit_ENH2B4");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, unit);
                db.AddInParameter(cmd, "@MAC_ADDRESS", DbType.String, mac_address);
                db.AddInParameter(cmd, "@JOB_DATE", DbType.String, job_date);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public byte[] GetCasaByUnit(string nip, string pass, string unit, string mac_address, DateTime job_date)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetCASAByUnit_ENH2B4");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, unit);
                db.AddInParameter(cmd, "@MAC_ADDRESS", DbType.String, mac_address);
                db.AddInParameter(cmd, "@JOB_DATE", DbType.String, job_date);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public byte[] GetCIF(string nip, string pass, string PROSPECT_CIF_ID, DateTime JOB_DATE)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetProsectCIF");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@PROSPECT_CIF_ID", DbType.String, PROSPECT_CIF_ID);
                db.AddInParameter(cmd, "@JOB_DATE", DbType.DateTime, JOB_DATE);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public byte[] GetCasa(string nip, string pass, string PROSPECT_CASA_ID, DateTime JOB_DATE)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetProsectCASA");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@PROSPECT_CASA_ID", DbType.String, PROSPECT_CASA_ID);
                db.AddInParameter(cmd, "@JOB_DATE", DbType.DateTime, JOB_DATE);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        bool ValidateDataSetEmpty(DataSet vDataSet)
        {
            if (vDataSet != null)
            {
                if (vDataSet.Tables[0].Rows.Count > 0)
                {
                    return true;
                }
            }
            return false;
        }


        [WebMethod]
        public int GetPRN(string nip, string pass, string CIF, ref string getPRN)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(@"SELECT	DISTINCT APPLICATION_ID FROM TBL_PROSPECT_CASA WHERE	CIF_NUMBER = @CIF");

                db.AddInParameter(cmd, "@CIF", DbType.String, CIF);

                //object result = db.ExecuteScalar(cmd);
                //getPRN = result.ToString();

                DataSet ds;
                ds = db.ExecuteDataSet(cmd);

                foreach (DataRow dr in ds.Tables[0].Rows)
                    getPRN = dr["APPLICATION_ID"].ToString();

                return ds.Tables[0].Rows.Count;
            }
            return 0;
        }

        [WebMethod]
        public string UploadProspect(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_TMP
                                SET 
                                    PROSPECT_NO = @PROSPECT_NO, UNIT_CODE = @UNIT_CODE, SOURCE_CODE = @SOURCE_CODE
                                    ,AO_CODE = @AO_CODE, FULL_NAME = @FULL_NAME, KTP_NAME = @KTP_NAME
                                    ,KTP_NO = @KTP_NO, DATE_OF_BIRTH = @DATE_OF_BIRTH, PLACE_OF_BIRTH = @PLACE_OF_BIRTH
                                    ,MOTHER_NAME = @MOTHER_NAME, MALE_FEMALE = @MALE_FEMALE, MARITAL_STATUS = @MARITAL_STATUS
                                    ,SPOUSE_NAME = @SPOUSE_NAME, SPOUSE_DATE_OF_BIRTH = @SPOUSE_DATE_OF_BIRTH, NPWP_NO = @NPWP_NO
                                    ,KODYA_KAB_BUSINESS = @KODYA_KAB_BUSINESS, KECAMATAN_BUSINESS = @KECAMATAN_BUSINESS, KELURAHAN_BUSINESS = @KELURAHAN_BUSINESS
                                    ,ZIP_CODE_BUSINESS = @ZIP_CODE_BUSINESS, ADDRESS_BUSINESS = @ADDRESS_BUSINESS, RESIDENTIAL_STATUS = @RESIDENTIAL_STATUS
                                    ,RESIDENTIAL_PHONE = @RESIDENTIAL_PHONE, MOBILE_PHONE = @MOBILE_PHONE, OFFICE_STATUS = @OFFICE_STATUS
                                    ,OFFICE_PHONE = @OFFICE_PHONE, YEAR_OF_STAY = @YEAR_OF_STAY, JML_TANGGUNGAN = @JML_TANGGUNGAN
                                    ,JENIS_USAHA = @JENIS_USAHA, LAMA_USAHA = @LAMA_USAHA, OMSET_PER_HARI = @OMSET_PER_HARI
                                    ,ADA_REK_BANK = @ADA_REK_BANK, RENCANA_PLAFON = @RENCANA_PLAFON, SALES_CHANNEL = @SALES_CHANNEL
                                    ,PROSPECT_TYPE = @PROSPECT_TYPE, FUNDING_LENDING = @FUNDING_LENDING, RESTRUCT_COMPONENT = @RESTRUCT_COMPONENT
                                    ,PROGRESS_STATUS = @PROGRESS_STATUS, CYCLE_STATUS = @CYCLE_STATUS, INSTALLMENT = @INSTALLMENT
                                    ,FAP_DATE = @FAP_DATE, NO_OF_CALLS = @NO_OF_CALLS, IS_INTERESTED = @IS_INTERESTED
                                    ,NOT_INTERESTED_REASON = @NOT_INTERESTED_REASON, NOT_INTERESTED_NOTE = @NOT_INTERESTED_NOTE, KET_KENDALA = @KET_KENDALA
                                    ,SCM_NOTE = @SCM_NOTE, PRODUCT_TYPE = @PRODUCT_TYPE, CIF_NUMBER = @CIF_NUMBER
                                    ,KARAKTER = @KARAKTER, REPUTASI_DEBITUR = @REPUTASI_DEBITUR, RIWAYAT_DSP = @RIWAYAT_DSP
                                    ,TAKSIRAN_PLAFON = @TAKSIRAN_PLAFON, TAKSIRAN_USAHA = @TAKSIRAN_USAHA, IS_DOCUMENT_COMPLETED = @IS_DOCUMENT_COMPLETED
                                    ,UM_DECISION = @UM_DECISION, UM_DECISION_NOTE = @UM_DECISION_NOTE, UM_DECISION_DATE = @UM_DECISION_DATE
                                    ,LOAN_NUMBER = @LOAN_NUMBER, PRODUCT_CODE = @PRODUCT_CODE, CURRENT_OS = @CURRENT_OS
                                    ,CURRENT_INSTALLMENT = @CURRENT_INSTALLMENT, OFFER_PLAFON = @OFFER_PLAFON, OFFER_INSTALLMENT = @OFFER_INSTALLMENT
                                    ,OFFER_INTEREST = @OFFER_INTEREST, OFFER_TENOR = @OFFER_TENOR, OFFER_FRESH_MONEY = @OFFER_FRESH_MONEY
                                    ,PRIORITY = @PRIORITY, PIPELINE_START_TIME = @PIPELINE_START_TIME, PIPELINE_FINISH_TIME = @PIPELINE_FINISH_TIME
                                    ,PROSPECT_START_TIME = @PROSPECT_START_TIME, PROSPECT_FINISH_TIME = @PROSPECT_FINISH_TIME, BI_GOOD_UNTIL = @BI_GOOD_UNTIL
                                    ,IS_BI_CHECKING = @IS_BI_CHECKING, IS_OFFLINE_ENTRY = @IS_OFFLINE_ENTRY, ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE, UPDATE_BY = @UPDATE_BY, UPDATE_DATE = @UPDATE_DATE
                                    ,IS_BY_UPLOAD = @IS_BY_UPLOAD, UPLOAD_BY = @UPLOAD_BY, UPLOAD_DATE = @UPLOAD_DATE
                                    ,IS_DELETED = @IS_DELETED, DEBTOR_TYPE = @DEBTOR_TYPE, GU_FUP = @GU_FUP
                                    ,GU_CLOSE_DEAL = @GU_CLOSE_DEAL, IS_DEDUP = @IS_DEDUP, CO_ASSIGNED = @CO_ASSIGNED
                                    ,CHANNELING = @CHANNELING, OMZET_1 = @OMZET_1, OMZET_2 = @OMZET_2
                                    ,OMZET_3 = @OMZET_3, OMZET_4 = @OMZET_4, OMZET_5 = @OMZET_5
                                    ,OMZET_6 = @OMZET_6, OMZET_7 = @OMZET_7, OMZET_8 = @OMZET_8
                                    ,OMZET_9 = @OMZET_9, OMZET_10 = @OMZET_10, OMZET_11 = @OMZET_11
                                    ,OMZET_12 = @OMZET_12, OMZET_AVERAGE = @OMZET_AVERAGE, IS_PIPELINE_KP = @IS_PIPELINE_KP
                                    ,[EKSTERNAL] = @EKSTERNAL, REFERENSI = @REFERENSI, RES_1 = @RES_1
                                    ,RES_2 = @RES_2, RES_3 = @RES_3, RES_4 = @RES_4
                                    ,RES_5 = @RES_5, RES_6 = @RES_6, RES_7 = @RES_7
                                    ,RES_8 = @RES_8, RES_9 = @RES_9, RES_10 = @RES_10
                                    ,RES_11 = @RES_11, RES_12 = @RES_12, RES_13 = @RES_13
                                    ,RES_14 = @RES_14, RES_15 = @RES_15, RES_16 = @RES_16
                                    ,RES_17 = @RES_17, RES_18 = @RES_18, RES_19 = @RES_19
                                    ,RES_20 = @RES_20, RES_21 = @RES_21, RES_22 = @RES_22
                                    ,RES_23 = @RES_23, RES_24 = @RES_24, RES_25 = @RES_25
                                    ,RES_26 = @RES_26, RES_27 = @RES_27, RES_28 = @RES_28
                                    ,RES_29 = @RES_29, RES_30 = @RES_30, RES_31 = @RES_31
                                    ,RES_32 = @RES_32, RES_33 = @RES_33, RES_34 = @RES_34
                                    ,RES_35 = @RES_35, RES_36 = @RES_36, RES_37 = @RES_37
                                    ,RES_38 = @RES_38, RES_39 = @RES_39, RES_40 = @RES_40
                                    ,RES_41 = @RES_41, RES_42 = @RES_42, RES_43 = @RES_43
                                    ,RES_44 = @RES_44, RES_45 = @RES_45, RES_46 = @RES_46
                                    ,RES_47 = @RES_47, RES_48 = @RES_48, RES_49 = @RES_49
                                    ,RES_50 = @RES_50, RES_51 = @RES_51, RES_52 = @RES_52
                                    ,RES_53 = @RES_53, RES_54 = @RES_54, RES_55 = @RES_55
                                    ,RES_56 = @RES_56, RES_57 = @RES_57, RES_58 = @RES_58
                                    ,RES_59 = @RES_59, RES_60 = @RES_60, RES_61 = @RES_61
                                    ,RES_62 = @RES_62, RES_63 = @RES_63, RES_64 = @RES_64
                                    ,RES_65 = @RES_65, RES_66 = @RES_66, RES_67 = @RES_67
                                    ,RES_68 = @RES_68, RES_69 = @RES_69, RES_70 = @RES_70
                                    ,RES_71 = @RES_71, RES_72 = @RES_72, RES_73 = @RES_73
                                    ,RES_74 = @RES_74, RES_75 = @RES_75, RES_76 = @RES_76
                                    ,RES_77 = @RES_77, RES_78 = @RES_78, RES_79 = @RES_79
                                    ,RES_80 = @RES_80, RES_81 = @RES_81, RES_82 = @RES_82
                                    ,RES_83 = @RES_83, RES_84 = @RES_84, RES_85 = @RES_85
                                    ,RES_86 = @RES_86, RES_87 = @RES_87, RES_88 = @RES_88
                                    ,RES_89 = @RES_89, RES_90 = @RES_90, RES_91 = @RES_91
                                    ,RES_92 = @RES_92, RES_93 = @RES_93, RES_94 = @RES_94
                                    ,RES_95 = @RES_95, RES_96 = @RES_96, RES_97 = @RES_97
                                    ,RES_98 = @RES_98, RES_99 = @RES_99, RES_100 = @RES_100
                                    ,NIP = @NIP, OLD_NIP = @OLD_NIP, OLD_AO_CODE = @OLD_AO_CODE
                                    ,ALTERNATE_FOR = @ALTERNATE_FOR, IS_UPLOAD = @IS_UPLOAD, FLAG_FREEZE = @FLAG_FREEZE
                                    ,KTP_ADDRESS = @KTP_ADDRESS, AVG_EXPENSE = @AVG_EXPENSE
                                WHERE (PROSPECT_ID = @PROSPECT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_TMP
                                (
                                    PROSPECT_ID,PROSPECT_NO,UNIT_CODE,SOURCE_CODE,AO_CODE
                                    ,FULL_NAME,KTP_NAME,KTP_NO,DATE_OF_BIRTH,PLACE_OF_BIRTH
                                    ,MOTHER_NAME,MALE_FEMALE,MARITAL_STATUS,SPOUSE_NAME,SPOUSE_DATE_OF_BIRTH
                                    ,NPWP_NO,KODYA_KAB_BUSINESS,KECAMATAN_BUSINESS,KELURAHAN_BUSINESS,ZIP_CODE_BUSINESS
                                    ,ADDRESS_BUSINESS,RESIDENTIAL_STATUS,RESIDENTIAL_PHONE,MOBILE_PHONE,OFFICE_STATUS
                                    ,OFFICE_PHONE,YEAR_OF_STAY,JML_TANGGUNGAN,JENIS_USAHA,LAMA_USAHA
                                    ,OMSET_PER_HARI,ADA_REK_BANK,RENCANA_PLAFON,SALES_CHANNEL,PROSPECT_TYPE
                                    ,FUNDING_LENDING,RESTRUCT_COMPONENT,PROGRESS_STATUS,CYCLE_STATUS,INSTALLMENT
                                    ,FAP_DATE,NO_OF_CALLS,IS_INTERESTED,NOT_INTERESTED_REASON,NOT_INTERESTED_NOTE
                                    ,KET_KENDALA,SCM_NOTE,PRODUCT_TYPE,CIF_NUMBER,KARAKTER
                                    ,REPUTASI_DEBITUR,RIWAYAT_DSP,TAKSIRAN_PLAFON,TAKSIRAN_USAHA,IS_DOCUMENT_COMPLETED
                                    ,UM_DECISION,UM_DECISION_NOTE,UM_DECISION_DATE,LOAN_NUMBER,PRODUCT_CODE
                                    ,CURRENT_OS,CURRENT_INSTALLMENT,OFFER_PLAFON,OFFER_INSTALLMENT,OFFER_INTEREST
                                    ,OFFER_TENOR,OFFER_FRESH_MONEY,PRIORITY,PIPELINE_START_TIME,PIPELINE_FINISH_TIME
                                    ,PROSPECT_START_TIME,PROSPECT_FINISH_TIME,BI_GOOD_UNTIL,IS_BI_CHECKING,IS_OFFLINE_ENTRY
                                    ,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,IS_BY_UPLOAD
                                    ,UPLOAD_BY,UPLOAD_DATE,IS_DELETED,DEBTOR_TYPE,GU_FUP
                                    ,GU_CLOSE_DEAL,IS_DEDUP,CO_ASSIGNED,CHANNELING,OMZET_1
                                    ,OMZET_2,OMZET_3,OMZET_4,OMZET_5,OMZET_6
                                    ,OMZET_7,OMZET_8,OMZET_9,OMZET_10,OMZET_11
                                    ,OMZET_12,OMZET_AVERAGE,IS_PIPELINE_KP,[EKSTERNAL],REFERENSI
                                    ,RES_1,RES_2,RES_3,RES_4,RES_5
                                    ,RES_6,RES_7,RES_8,RES_9,RES_10
                                    ,RES_11,RES_12,RES_13,RES_14,RES_15
                                    ,RES_16,RES_17,RES_18,RES_19,RES_20
                                    ,RES_21,RES_22,RES_23,RES_24,RES_25
                                    ,RES_26,RES_27,RES_28,RES_29,RES_30
                                    ,RES_31,RES_32,RES_33,RES_34,RES_35
                                    ,RES_36,RES_37,RES_38,RES_39,RES_40
                                    ,RES_41,RES_42,RES_43,RES_44,RES_45
                                    ,RES_46,RES_47,RES_48,RES_49,RES_50
                                    ,RES_51,RES_52,RES_53,RES_54,RES_55
                                    ,RES_56,RES_57,RES_58,RES_59,RES_60
                                    ,RES_61,RES_62,RES_63,RES_64,RES_65
                                    ,RES_66,RES_67,RES_68,RES_69,RES_70
                                    ,RES_71,RES_72,RES_73,RES_74,RES_75
                                    ,RES_76,RES_77,RES_78,RES_79,RES_80
                                    ,RES_81,RES_82,RES_83,RES_84,RES_85
                                    ,RES_86,RES_87,RES_88,RES_89,RES_90
                                    ,RES_91,RES_92,RES_93,RES_94,RES_95
                                    ,RES_96,RES_97,RES_98,RES_99,RES_100
                                    ,NIP,OLD_NIP,OLD_AO_CODE,ALTERNATE_FOR,IS_UPLOAD
                                    ,FLAG_FREEZE
                                    ,KTP_ADDRESS, AVG_EXPENSE) 
                                VALUES
                                (
                                    @PROSPECT_ID,@PROSPECT_NO,@UNIT_CODE,@SOURCE_CODE,@AO_CODE
                                    ,@FULL_NAME,@KTP_NAME,@KTP_NO,@DATE_OF_BIRTH,@PLACE_OF_BIRTH
                                    ,@MOTHER_NAME,@MALE_FEMALE,@MARITAL_STATUS,@SPOUSE_NAME,@SPOUSE_DATE_OF_BIRTH
                                    ,@NPWP_NO,@KODYA_KAB_BUSINESS,@KECAMATAN_BUSINESS,@KELURAHAN_BUSINESS,@ZIP_CODE_BUSINESS
                                    ,@ADDRESS_BUSINESS,@RESIDENTIAL_STATUS,@RESIDENTIAL_PHONE,@MOBILE_PHONE,@OFFICE_STATUS
                                    ,@OFFICE_PHONE,@YEAR_OF_STAY,@JML_TANGGUNGAN,@JENIS_USAHA,@LAMA_USAHA
                                    ,@OMSET_PER_HARI,@ADA_REK_BANK,@RENCANA_PLAFON,@SALES_CHANNEL,@PROSPECT_TYPE
                                    ,@FUNDING_LENDING,@RESTRUCT_COMPONENT,@PROGRESS_STATUS,@CYCLE_STATUS,@INSTALLMENT
                                    ,@FAP_DATE,@NO_OF_CALLS,@IS_INTERESTED,@NOT_INTERESTED_REASON,@NOT_INTERESTED_NOTE
                                    ,@KET_KENDALA,@SCM_NOTE,@PRODUCT_TYPE,@CIF_NUMBER,@KARAKTER
                                    ,@REPUTASI_DEBITUR,@RIWAYAT_DSP,@TAKSIRAN_PLAFON,@TAKSIRAN_USAHA,@IS_DOCUMENT_COMPLETED
                                    ,@UM_DECISION,@UM_DECISION_NOTE,@UM_DECISION_DATE,@LOAN_NUMBER,@PRODUCT_CODE
                                    ,@CURRENT_OS,@CURRENT_INSTALLMENT,@OFFER_PLAFON,@OFFER_INSTALLMENT,@OFFER_INTEREST
                                    ,@OFFER_TENOR,@OFFER_FRESH_MONEY,@PRIORITY,@PIPELINE_START_TIME,@PIPELINE_FINISH_TIME
                                    ,@PROSPECT_START_TIME,@PROSPECT_FINISH_TIME,@BI_GOOD_UNTIL,@IS_BI_CHECKING,@IS_OFFLINE_ENTRY
                                    ,@ENTRY_BY,@ENTRY_DATE,@UPDATE_BY,@UPDATE_DATE,@IS_BY_UPLOAD
                                    ,@UPLOAD_BY,@UPLOAD_DATE,@IS_DELETED,@DEBTOR_TYPE,@GU_FUP
                                    ,@GU_CLOSE_DEAL,@IS_DEDUP,@CO_ASSIGNED,@CHANNELING,@OMZET_1
                                    ,@OMZET_2,@OMZET_3,@OMZET_4,@OMZET_5,@OMZET_6
                                    ,@OMZET_7,@OMZET_8,@OMZET_9,@OMZET_10,@OMZET_11
                                    ,@OMZET_12,@OMZET_AVERAGE,@IS_PIPELINE_KP,@EKSTERNAL,@REFERENSI
                                    ,@RES_1,@RES_2,@RES_3,@RES_4,@RES_5
                                    ,@RES_6,@RES_7,@RES_8,@RES_9,@RES_10
                                    ,@RES_11,@RES_12,@RES_13,@RES_14,@RES_15
                                    ,@RES_16,@RES_17,@RES_18,@RES_19,@RES_20
                                    ,@RES_21,@RES_22,@RES_23,@RES_24,@RES_25
                                    ,@RES_26,@RES_27,@RES_28,@RES_29,@RES_30
                                    ,@RES_31,@RES_32,@RES_33,@RES_34,@RES_35
                                    ,@RES_36,@RES_37,@RES_38,@RES_39,@RES_40
                                    ,@RES_41,@RES_42,@RES_43,@RES_44,@RES_45
                                    ,@RES_46,@RES_47,@RES_48,@RES_49,@RES_50
                                    ,@RES_51,@RES_52,@RES_53,@RES_54,@RES_55
                                    ,@RES_56,@RES_57,@RES_58,@RES_59,@RES_60
                                    ,@RES_61,@RES_62,@RES_63,@RES_64,@RES_65
                                    ,@RES_66,@RES_67,@RES_68,@RES_69,@RES_70
                                    ,@RES_71,@RES_72,@RES_73,@RES_74,@RES_75
                                    ,@RES_76,@RES_77,@RES_78,@RES_79,@RES_80
                                    ,@RES_81,@RES_82,@RES_83,@RES_84,@RES_85
                                    ,@RES_86,@RES_87,@RES_88,@RES_89,@RES_90
                                    ,@RES_91,@RES_92,@RES_93,@RES_94,@RES_95
                                    ,@RES_96,@RES_97,@RES_98,@RES_99,@RES_100
                                    ,@NIP,@OLD_NIP,@OLD_AO_CODE,@ALTERNATE_FOR,@IS_UPLOAD
                                    ,@FLAG_FREEZE
                                    ,@KTP_ADDRESS, @AVG_EXPENSE
                                );
                            ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, Convert.ToString(dr["PROSPECT_NO"]));
                        db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                        db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, Convert.ToString(dr["SOURCE_CODE"]));
                        db.AddInParameter(cmd, "@AO_CODE", DbType.String, Convert.ToString(dr["AO_CODE"]));
                        db.AddInParameter(cmd, "@FULL_NAME", DbType.String, Convert.ToString(dr["FULL_NAME"]));
                        db.AddInParameter(cmd, "@KTP_NAME", DbType.String, Convert.ToString(dr["KTP_NAME"]));
                        db.AddInParameter(cmd, "@KTP_NO", DbType.String, Convert.ToString(dr["KTP_NO"]));
                        db.AddInParameter(cmd, "@DATE_OF_BIRTH", DbType.DateTime, Convert.ToDateTime(dr["DATE_OF_BIRTH"]));
                        db.AddInParameter(cmd, "@PLACE_OF_BIRTH", DbType.String, Convert.ToString(dr["PLACE_OF_BIRTH"]));
                        db.AddInParameter(cmd, "@MOTHER_NAME", DbType.String, Convert.ToString(dr["MOTHER_NAME"]));
                        db.AddInParameter(cmd, "@MALE_FEMALE", DbType.String, Convert.ToString(dr["MALE_FEMALE"]));
                        db.AddInParameter(cmd, "@MARITAL_STATUS", DbType.String, Convert.ToString(dr["MARITAL_STATUS"]));
                        db.AddInParameter(cmd, "@SPOUSE_NAME", DbType.String, Convert.ToString(dr["SPOUSE_NAME"]));
                        db.AddInParameter(cmd, "@SPOUSE_DATE_OF_BIRTH", DbType.DateTime, Convert.ToDateTime(dr["SPOUSE_DATE_OF_BIRTH"]));
                        db.AddInParameter(cmd, "@NPWP_NO", DbType.String, Convert.ToString(dr["NPWP_NO"]));
                        db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, Convert.ToString(dr["KODYA_KAB_BUSINESS"]));
                        db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, Convert.ToString(dr["KECAMATAN_BUSINESS"]));
                        db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, Convert.ToString(dr["KELURAHAN_BUSINESS"]));
                        db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, Convert.ToString(dr["ZIP_CODE_BUSINESS"]));
                        db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, Convert.ToString(dr["ADDRESS_BUSINESS"]));
                        db.AddInParameter(cmd, "@RESIDENTIAL_STATUS", DbType.String, Convert.ToString(dr["RESIDENTIAL_STATUS"]));
                        db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, Convert.ToString(dr["RESIDENTIAL_PHONE"]));
                        db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, Convert.ToString(dr["MOBILE_PHONE"]));
                        db.AddInParameter(cmd, "@OFFICE_STATUS", DbType.String, Convert.ToString(dr["OFFICE_STATUS"]));
                        db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, Convert.ToString(dr["OFFICE_PHONE"]));
                        db.AddInParameter(cmd, "@YEAR_OF_STAY", DbType.Int16, Convert.ToInt16(dr["YEAR_OF_STAY"]));
                        db.AddInParameter(cmd, "@JML_TANGGUNGAN", DbType.Int16, Convert.ToInt16(dr["JML_TANGGUNGAN"]));
                        db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, Convert.ToString(dr["JENIS_USAHA"]));
                        db.AddInParameter(cmd, "@LAMA_USAHA", DbType.String, Convert.ToString(dr["LAMA_USAHA"]));
                        db.AddInParameter(cmd, "@OMSET_PER_HARI", DbType.Int32, Convert.ToInt32(dr["OMSET_PER_HARI"]));
                        db.AddInParameter(cmd, "@ADA_REK_BANK", DbType.String, Convert.ToString(dr["ADA_REK_BANK"]));
                        db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, Convert.ToDouble(dr["RENCANA_PLAFON"]));
                        db.AddInParameter(cmd, "@SALES_CHANNEL", DbType.String, Convert.ToString(dr["SALES_CHANNEL"]));
                        db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, Convert.ToString(dr["PROSPECT_TYPE"]));
                        db.AddInParameter(cmd, "@FUNDING_LENDING", DbType.String, Convert.ToString(dr["FUNDING_LENDING"]));
                        db.AddInParameter(cmd, "@RESTRUCT_COMPONENT", DbType.String, Convert.ToString(dr["RESTRUCT_COMPONENT"]));
                        db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, Convert.ToString(dr["PROGRESS_STATUS"]));
                        db.AddInParameter(cmd, "@CYCLE_STATUS", DbType.String, Convert.ToString(dr["CYCLE_STATUS"]));
                        db.AddInParameter(cmd, "@INSTALLMENT", DbType.Double, Convert.ToDouble(dr["INSTALLMENT"]));
                        db.AddInParameter(cmd, "@FAP_DATE", DbType.DateTime, Convert.ToDateTime(dr["FAP_DATE"]));
                        db.AddInParameter(cmd, "@NO_OF_CALLS", DbType.Int16, Convert.ToString(dr["RESTRUCT_COMPONENT"]).Trim() == "1" ? Convert.ToInt16(dr["NO_OF_CALLS"]) + 1 : Convert.ToInt16(dr["NO_OF_CALLS"]));
                        db.AddInParameter(cmd, "@IS_INTERESTED", DbType.String, Convert.ToString(dr["IS_INTERESTED"]));
                        db.AddInParameter(cmd, "@NOT_INTERESTED_REASON", DbType.String, Convert.ToString(dr["NOT_INTERESTED_REASON"]));
                        db.AddInParameter(cmd, "@NOT_INTERESTED_NOTE", DbType.String, Convert.ToString(dr["NOT_INTERESTED_NOTE"]));
                        db.AddInParameter(cmd, "@KET_KENDALA", DbType.String, Convert.ToString(dr["KET_KENDALA"]));
                        db.AddInParameter(cmd, "@SCM_NOTE", DbType.String, Convert.ToString(dr["SCM_NOTE"]));
                        db.AddInParameter(cmd, "@PRODUCT_TYPE", DbType.Int16, Convert.ToInt16(dr["PRODUCT_TYPE"]));
                        db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int32, Convert.ToInt32(dr["CIF_NUMBER"]));
                        db.AddInParameter(cmd, "@KARAKTER", DbType.String, Convert.ToString(dr["KARAKTER"]));
                        db.AddInParameter(cmd, "@REPUTASI_DEBITUR", DbType.String, Convert.ToString(dr["REPUTASI_DEBITUR"]));
                        db.AddInParameter(cmd, "@RIWAYAT_DSP", DbType.String, Convert.ToString(dr["RIWAYAT_DSP"]));
                        db.AddInParameter(cmd, "@TAKSIRAN_PLAFON", DbType.Double, Convert.ToDouble(dr["TAKSIRAN_PLAFON"]));
                        db.AddInParameter(cmd, "@TAKSIRAN_USAHA", DbType.String, Convert.ToString(dr["TAKSIRAN_USAHA"]));
                        db.AddInParameter(cmd, "@IS_DOCUMENT_COMPLETED", DbType.String, Convert.ToString(dr["IS_DOCUMENT_COMPLETED"]));
                        db.AddInParameter(cmd, "@UM_DECISION", DbType.String, Convert.ToString(dr["UM_DECISION"]));
                        db.AddInParameter(cmd, "@UM_DECISION_NOTE", DbType.String, Convert.ToString(dr["UM_DECISION_NOTE"]));
                        db.AddInParameter(cmd, "@UM_DECISION_DATE", DbType.DateTime, Convert.ToDateTime(dr["UM_DECISION_DATE"]));
                        db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, Convert.ToString(dr["LOAN_NUMBER"]));
                        db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.String, Convert.ToString(dr["PRODUCT_CODE"]));
                        db.AddInParameter(cmd, "@CURRENT_OS", DbType.Double, Convert.ToDouble(dr["CURRENT_OS"]));
                        db.AddInParameter(cmd, "@CURRENT_INSTALLMENT", DbType.Double, Convert.ToDouble(dr["CURRENT_INSTALLMENT"]));
                        db.AddInParameter(cmd, "@OFFER_PLAFON", DbType.Double, Convert.ToDouble(dr["OFFER_PLAFON"]));
                        db.AddInParameter(cmd, "@OFFER_INSTALLMENT", DbType.Double, Convert.ToDouble(dr["OFFER_INSTALLMENT"]));
                        db.AddInParameter(cmd, "@OFFER_INTEREST", DbType.String, Convert.ToString(dr["OFFER_INTEREST"]));
                        db.AddInParameter(cmd, "@OFFER_TENOR", DbType.Int16, Convert.ToInt16(dr["OFFER_TENOR"]));
                        db.AddInParameter(cmd, "@OFFER_FRESH_MONEY", DbType.Double, Convert.ToDouble(dr["OFFER_FRESH_MONEY"]));
                        db.AddInParameter(cmd, "@PRIORITY", DbType.String, Convert.ToString(dr["PRIORITY"]));
                        db.AddInParameter(cmd, "@PIPELINE_START_TIME", DbType.DateTime, Convert.ToDateTime(dr["PIPELINE_START_TIME"]));
                        db.AddInParameter(cmd, "@PIPELINE_FINISH_TIME", DbType.DateTime, Convert.ToDateTime(dr["PIPELINE_FINISH_TIME"]));
                        db.AddInParameter(cmd, "@PROSPECT_START_TIME", DbType.DateTime, Convert.ToDateTime(dr["PROSPECT_START_TIME"]));
                        db.AddInParameter(cmd, "@PROSPECT_FINISH_TIME", DbType.DateTime, Convert.ToDateTime(dr["PROSPECT_FINISH_TIME"]));
                        db.AddInParameter(cmd, "@BI_GOOD_UNTIL", DbType.DateTime, Convert.ToDateTime(dr["BI_GOOD_UNTIL"]));
                        db.AddInParameter(cmd, "@IS_BI_CHECKING", DbType.String, Convert.ToString(dr["IS_BI_CHECKING"]));
                        db.AddInParameter(cmd, "@IS_OFFLINE_ENTRY", DbType.String, Convert.ToString(dr["IS_OFFLINE_ENTRY"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@IS_BY_UPLOAD", DbType.String, Convert.ToString(dr["IS_BY_UPLOAD"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        db.AddInParameter(cmd, "@IS_DELETED", DbType.String, Convert.ToString(dr["IS_DELETED"]));
                        db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, Convert.ToString(dr["DEBTOR_TYPE"]));
                        db.AddInParameter(cmd, "@GU_FUP", DbType.DateTime, Convert.ToDateTime(dr["GU_FUP"]));
                        db.AddInParameter(cmd, "@GU_CLOSE_DEAL", DbType.DateTime, Convert.ToDateTime(dr["GU_CLOSE_DEAL"]));
                        db.AddInParameter(cmd, "@IS_DEDUP", DbType.String, Convert.ToString(dr["IS_DEDUP"]));
                        db.AddInParameter(cmd, "@CO_ASSIGNED", DbType.String, Convert.ToString(dr["CO_ASSIGNED"]));
                        db.AddInParameter(cmd, "@CHANNELING", DbType.String, Convert.ToString(dr["CHANNELING"]));
                        db.AddInParameter(cmd, "@OMZET_1", DbType.Double, Convert.ToDouble(dr["OMZET_1"]));
                        db.AddInParameter(cmd, "@OMZET_2", DbType.Double, Convert.ToDouble(dr["OMZET_2"]));
                        db.AddInParameter(cmd, "@OMZET_3", DbType.Double, Convert.ToDouble(dr["OMZET_3"]));
                        db.AddInParameter(cmd, "@OMZET_4", DbType.Double, Convert.ToDouble(dr["OMZET_4"]));
                        db.AddInParameter(cmd, "@OMZET_5", DbType.Double, Convert.ToDouble(dr["OMZET_5"]));
                        db.AddInParameter(cmd, "@OMZET_6", DbType.Double, Convert.ToDouble(dr["OMZET_6"]));
                        db.AddInParameter(cmd, "@OMZET_7", DbType.Double, Convert.ToDouble(dr["OMZET_7"]));
                        db.AddInParameter(cmd, "@OMZET_8", DbType.Double, Convert.ToDouble(dr["OMZET_8"]));
                        db.AddInParameter(cmd, "@OMZET_9", DbType.Double, Convert.ToDouble(dr["OMZET_9"]));
                        db.AddInParameter(cmd, "@OMZET_10", DbType.Double, Convert.ToDouble(dr["OMZET_10"]));
                        db.AddInParameter(cmd, "@OMZET_11", DbType.Double, Convert.ToDouble(dr["OMZET_11"]));
                        db.AddInParameter(cmd, "@OMZET_12", DbType.Double, Convert.ToDouble(dr["OMZET_12"]));
                        db.AddInParameter(cmd, "@OMZET_AVERAGE", DbType.Double, Convert.ToDouble(dr["OMZET_AVERAGE"]));
                        db.AddInParameter(cmd, "@IS_PIPELINE_KP", DbType.String, Convert.ToString(dr["IS_PIPELINE_KP"]));
                        db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, Convert.ToString(dr["EKSTERNAL"]));
                        db.AddInParameter(cmd, "@REFERENSI", DbType.String, Convert.ToString(dr["REFERENSI"]));
                        db.AddInParameter(cmd, "@RES_1", DbType.String, Convert.ToString(dr["RES_1"]));
                        db.AddInParameter(cmd, "@RES_2", DbType.String, Convert.ToString(dr["RES_2"]));
                        db.AddInParameter(cmd, "@RES_3", DbType.String, Convert.ToString(dr["RES_3"]));
                        db.AddInParameter(cmd, "@RES_4", DbType.String, Convert.ToString(dr["RES_4"]));
                        db.AddInParameter(cmd, "@RES_5", DbType.String, Convert.ToString(dr["RES_5"]));
                        db.AddInParameter(cmd, "@RES_6", DbType.String, Convert.ToString(dr["RES_6"]));
                        db.AddInParameter(cmd, "@RES_7", DbType.String, Convert.ToString(dr["RES_7"]));
                        db.AddInParameter(cmd, "@RES_8", DbType.String, Convert.ToString(dr["RES_8"]));
                        db.AddInParameter(cmd, "@RES_9", DbType.String, Convert.ToString(dr["RES_9"]));
                        db.AddInParameter(cmd, "@RES_10", DbType.String, Convert.ToString(dr["RES_10"]));
                        db.AddInParameter(cmd, "@RES_11", DbType.String, Convert.ToString(dr["RES_11"]));
                        db.AddInParameter(cmd, "@RES_12", DbType.String, Convert.ToString(dr["RES_12"]));
                        db.AddInParameter(cmd, "@RES_13", DbType.String, Convert.ToString(dr["RES_13"]));
                        db.AddInParameter(cmd, "@RES_14", DbType.String, Convert.ToString(dr["RES_14"]));
                        db.AddInParameter(cmd, "@RES_15", DbType.String, Convert.ToString(dr["RES_15"]));
                        db.AddInParameter(cmd, "@RES_16", DbType.String, Convert.ToString(dr["RES_16"]));
                        db.AddInParameter(cmd, "@RES_17", DbType.String, Convert.ToString(dr["RES_17"]));
                        db.AddInParameter(cmd, "@RES_18", DbType.String, Convert.ToString(dr["RES_18"]));
                        db.AddInParameter(cmd, "@RES_19", DbType.String, Convert.ToString(dr["RES_19"]));
                        db.AddInParameter(cmd, "@RES_20", DbType.String, Convert.ToString(dr["RES_20"]));
                        db.AddInParameter(cmd, "@RES_21", DbType.String, Convert.ToString(dr["RES_21"]));
                        db.AddInParameter(cmd, "@RES_22", DbType.String, Convert.ToString(dr["RES_22"]));
                        db.AddInParameter(cmd, "@RES_23", DbType.String, Convert.ToString(dr["RES_23"]));
                        db.AddInParameter(cmd, "@RES_24", DbType.String, Convert.ToString(dr["RES_24"]));
                        db.AddInParameter(cmd, "@RES_25", DbType.String, Convert.ToString(dr["RES_25"]));
                        db.AddInParameter(cmd, "@RES_26", DbType.DateTime, Convert.ToDateTime(dr["RES_26"]));
                        db.AddInParameter(cmd, "@RES_27", DbType.DateTime, Convert.ToDateTime(dr["RES_27"]));
                        db.AddInParameter(cmd, "@RES_28", DbType.DateTime, Convert.ToDateTime(dr["RES_28"]));
                        db.AddInParameter(cmd, "@RES_29", DbType.DateTime, Convert.ToDateTime(dr["RES_29"]));
                        db.AddInParameter(cmd, "@RES_30", DbType.DateTime, Convert.ToDateTime(dr["RES_30"]));
                        db.AddInParameter(cmd, "@RES_31", DbType.DateTime, Convert.ToDateTime(dr["RES_31"]));
                        db.AddInParameter(cmd, "@RES_32", DbType.DateTime, Convert.ToDateTime(dr["RES_32"]));
                        db.AddInParameter(cmd, "@RES_33", DbType.DateTime, Convert.ToDateTime(dr["RES_33"]));
                        db.AddInParameter(cmd, "@RES_34", DbType.DateTime, Convert.ToDateTime(dr["RES_34"]));
                        db.AddInParameter(cmd, "@RES_35", DbType.DateTime, Convert.ToDateTime(dr["RES_35"]));
                        db.AddInParameter(cmd, "@RES_36", DbType.DateTime, Convert.ToDateTime(dr["RES_36"]));
                        db.AddInParameter(cmd, "@RES_37", DbType.DateTime, Convert.ToDateTime(dr["RES_37"]));
                        db.AddInParameter(cmd, "@RES_38", DbType.DateTime, Convert.ToDateTime(dr["RES_38"]));
                        db.AddInParameter(cmd, "@RES_39", DbType.DateTime, Convert.ToDateTime(dr["RES_39"]));
                        db.AddInParameter(cmd, "@RES_40", DbType.DateTime, Convert.ToDateTime(dr["RES_40"]));
                        db.AddInParameter(cmd, "@RES_41", DbType.DateTime, Convert.ToDateTime(dr["RES_41"]));
                        db.AddInParameter(cmd, "@RES_42", DbType.DateTime, Convert.ToDateTime(dr["RES_42"]));
                        db.AddInParameter(cmd, "@RES_43", DbType.DateTime, Convert.ToDateTime(dr["RES_43"]));
                        db.AddInParameter(cmd, "@RES_44", DbType.DateTime, Convert.ToDateTime(dr["RES_44"]));
                        db.AddInParameter(cmd, "@RES_45", DbType.DateTime, Convert.ToDateTime(dr["RES_45"]));
                        db.AddInParameter(cmd, "@RES_46", DbType.DateTime, Convert.ToDateTime(dr["RES_46"]));
                        db.AddInParameter(cmd, "@RES_47", DbType.DateTime, Convert.ToDateTime(dr["RES_47"]));
                        db.AddInParameter(cmd, "@RES_48", DbType.DateTime, Convert.ToDateTime(dr["RES_48"]));
                        db.AddInParameter(cmd, "@RES_49", DbType.DateTime, Convert.ToDateTime(dr["RES_49"]));
                        db.AddInParameter(cmd, "@RES_50", DbType.DateTime, Convert.ToDateTime(dr["RES_50"]));
                        db.AddInParameter(cmd, "@RES_51", DbType.Double, Convert.ToDouble(dr["RES_51"]));
                        db.AddInParameter(cmd, "@RES_52", DbType.Double, Convert.ToDouble(dr["RES_52"]));
                        db.AddInParameter(cmd, "@RES_53", DbType.Double, Convert.ToDouble(dr["RES_53"]));
                        db.AddInParameter(cmd, "@RES_54", DbType.Double, Convert.ToDouble(dr["RES_54"]));
                        db.AddInParameter(cmd, "@RES_55", DbType.Double, Convert.ToDouble(dr["RES_55"]));
                        db.AddInParameter(cmd, "@RES_56", DbType.Double, Convert.ToDouble(dr["RES_56"]));
                        db.AddInParameter(cmd, "@RES_57", DbType.Double, Convert.ToDouble(dr["RES_57"]));
                        db.AddInParameter(cmd, "@RES_58", DbType.Double, Convert.ToDouble(dr["RES_58"]));
                        db.AddInParameter(cmd, "@RES_59", DbType.Double, Convert.ToDouble(dr["RES_59"]));
                        db.AddInParameter(cmd, "@RES_60", DbType.Double, Convert.ToDouble(dr["RES_60"]));
                        db.AddInParameter(cmd, "@RES_61", DbType.Double, Convert.ToDouble(dr["RES_61"]));
                        db.AddInParameter(cmd, "@RES_62", DbType.Double, Convert.ToDouble(dr["RES_62"]));
                        db.AddInParameter(cmd, "@RES_63", DbType.Double, Convert.ToDouble(dr["RES_63"]));
                        db.AddInParameter(cmd, "@RES_64", DbType.Double, Convert.ToDouble(dr["RES_64"]));
                        db.AddInParameter(cmd, "@RES_65", DbType.Double, Convert.ToDouble(dr["RES_65"]));
                        db.AddInParameter(cmd, "@RES_66", DbType.Double, Convert.ToDouble(dr["RES_66"]));
                        db.AddInParameter(cmd, "@RES_67", DbType.Double, Convert.ToDouble(dr["RES_67"]));
                        db.AddInParameter(cmd, "@RES_68", DbType.Double, Convert.ToDouble(dr["RES_68"]));
                        db.AddInParameter(cmd, "@RES_69", DbType.Double, Convert.ToDouble(dr["RES_69"]));
                        db.AddInParameter(cmd, "@RES_70", DbType.Double, Convert.ToDouble(dr["RES_70"]));
                        db.AddInParameter(cmd, "@RES_71", DbType.Double, Convert.ToDouble(dr["RES_71"]));
                        db.AddInParameter(cmd, "@RES_72", DbType.Double, Convert.ToDouble(dr["RES_72"]));
                        db.AddInParameter(cmd, "@RES_73", DbType.Double, Convert.ToDouble(dr["RES_73"]));
                        db.AddInParameter(cmd, "@RES_74", DbType.Double, Convert.ToDouble(dr["RES_74"]));
                        db.AddInParameter(cmd, "@RES_75", DbType.Double, Convert.ToDouble(dr["RES_75"]));
                        db.AddInParameter(cmd, "@RES_76", DbType.String, Convert.ToString(dr["RES_76"]));
                        db.AddInParameter(cmd, "@RES_77", DbType.String, Convert.ToString(dr["RES_77"]));
                        db.AddInParameter(cmd, "@RES_78", DbType.String, Convert.ToString(dr["RES_78"]));
                        db.AddInParameter(cmd, "@RES_79", DbType.String, Convert.ToString(dr["RES_79"]));
                        db.AddInParameter(cmd, "@RES_80", DbType.String, Convert.ToString(dr["RES_80"]));
                        db.AddInParameter(cmd, "@RES_81", DbType.String, Convert.ToString(dr["RES_81"]));
                        db.AddInParameter(cmd, "@RES_82", DbType.String, Convert.ToString(dr["RES_82"]));
                        db.AddInParameter(cmd, "@RES_83", DbType.String, Convert.ToString(dr["RES_83"]));
                        db.AddInParameter(cmd, "@RES_84", DbType.String, Convert.ToString(dr["RES_84"]));
                        db.AddInParameter(cmd, "@RES_85", DbType.String, Convert.ToString(dr["RES_85"]));
                        db.AddInParameter(cmd, "@RES_86", DbType.String, Convert.ToString(dr["RES_86"]));
                        db.AddInParameter(cmd, "@RES_87", DbType.String, Convert.ToString(dr["RES_87"]));
                        db.AddInParameter(cmd, "@RES_88", DbType.String, Convert.ToString(dr["RES_88"]));
                        db.AddInParameter(cmd, "@RES_89", DbType.String, Convert.ToString(dr["RES_89"]));
                        db.AddInParameter(cmd, "@RES_90", DbType.String, Convert.ToString(dr["RES_90"]));
                        db.AddInParameter(cmd, "@RES_91", DbType.String, Convert.ToString(dr["RES_91"]));
                        db.AddInParameter(cmd, "@RES_92", DbType.String, Convert.ToString(dr["RES_92"]));
                        db.AddInParameter(cmd, "@RES_93", DbType.String, Convert.ToString(dr["RES_93"]));
                        db.AddInParameter(cmd, "@RES_94", DbType.String, Convert.ToString(dr["RES_94"]));
                        db.AddInParameter(cmd, "@RES_95", DbType.String, Convert.ToString(dr["RES_95"]));
                        db.AddInParameter(cmd, "@RES_96", DbType.String, Convert.ToString(dr["RES_96"]));
                        db.AddInParameter(cmd, "@RES_97", DbType.String, Convert.ToString(dr["RES_97"]));
                        db.AddInParameter(cmd, "@RES_98", DbType.String, Convert.ToString(dr["RES_98"]));
                        db.AddInParameter(cmd, "@RES_99", DbType.String, Convert.ToString(dr["RES_99"]));
                        db.AddInParameter(cmd, "@RES_100", DbType.String, Convert.ToString(dr["RES_100"]));
                        db.AddInParameter(cmd, "@NIP", DbType.String, Convert.ToString(dr["NIP"]));
                        db.AddInParameter(cmd, "@OLD_NIP", DbType.String, Convert.ToString(dr["OLD_NIP"]));
                        db.AddInParameter(cmd, "@OLD_AO_CODE", DbType.String, Convert.ToString(dr["OLD_AO_CODE"]));
                        db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, Convert.ToString(dr["ALTERNATE_FOR"]));
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");
                        db.AddInParameter(cmd, "@FLAG_FREEZE", DbType.Int16, Convert.ToInt16(dr["FLAG_FREEZE"]));

                        db.AddInParameter(cmd, "@KTP_ADDRESS", DbType.String, Convert.ToString(dr["KTP_ADDRESS"]));
                        db.AddInParameter(cmd, "@AVG_EXPENSE", DbType.Double, Convert.ToDouble(dr["AVG_EXPENSE"]));

                        if (db.ExecuteNonQuery(cmd) > 0)
                        {
                            result++;

                            /*
                            cmd = db.GetSqlStringCommand(@"
                               IF (@PROGRESS_STATUS = 'RJS' OR @PROGRESS_STATUS = 'RJD')
                               BEGIN
                                   DELETE FROM TBL_PROSPECT_DEDUP 
                                   WHERE [PROSPECT_ID] = @PROSPECT_ID
                               END

                               ELSE

                               BEGIN
                                UPDATE [TBL_PROSPECT_DEDUP]
                                   SET [PROSPECT_NO] = @PROSPECT_NO
                                      ,[UNIT_CODE] = @UNIT_CODE
                                      ,[SOURCE_CODE] = @SOURCE_CODE
                                      ,[AO_CODE] = @AO_CODE
                                      ,[KTP_NAME] = @KTP_NAME
                                      ,[KTP_NO] = @KTP_NO
                                      ,[DATE_OF_BIRTH] = @DATE_OF_BIRTH
                                      ,[PLACE_OF_BIRTH] = @PLACE_OF_BIRTH
                                      ,[ADDRESS_BUSINESS] = @ADDRESS_BUSINESS
                                      ,[RESIDENTIAL_PHONE] = @RESIDENTIAL_PHONE
                                      ,[MOBILE_PHONE] = @MOBILE_PHONE
                                      ,[OFFICE_PHONE] = @OFFICE_PHONE
                                      ,[PRODUCT_TYPE] = @PRODUCT_TYPE
                                      ,[PROGRESS_STATUS] = @PROGRESS_STATUS
                                      ,[UPDATE_DATE] = @UPDATE_DATE
                                 WHERE [PROSPECT_ID] = @PROSPECT_ID

                                 IF @@ROWCOUNT = 0
                                 INSERT INTO [TBL_PROSPECT_DEDUP]
                                    ([PROSPECT_ID],[PROSPECT_NO],[UNIT_CODE],[SOURCE_CODE],[AO_CODE]
                                    ,[KTP_NAME],[KTP_NO],[DATE_OF_BIRTH],[PLACE_OF_BIRTH],[ADDRESS_BUSINESS]
                                    ,[RESIDENTIAL_PHONE],[MOBILE_PHONE],[OFFICE_PHONE],[PRODUCT_TYPE],[PROGRESS_STATUS]
                                    ,[UPDATE_DATE])
                                 VALUES
                                   (@PROSPECT_ID,@PROSPECT_NO,@UNIT_CODE,@SOURCE_CODE,@AO_CODE
                                   ,@KTP_NAME,@KTP_NO,@DATE_OF_BIRTH,@PLACE_OF_BIRTH,@ADDRESS_BUSINESS
                                   ,@RESIDENTIAL_PHONE,@MOBILE_PHONE,@OFFICE_PHONE,@PRODUCT_TYPE,@PROGRESS_STATUS
                                   ,@UPDATE_DATE)
                               END ");

                            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                            db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, Convert.ToString(dr["PROSPECT_NO"]));
                            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                            db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, Convert.ToString(dr["SOURCE_CODE"]));
                            db.AddInParameter(cmd, "@AO_CODE", DbType.String, Convert.ToString(dr["AO_CODE"]));
                            db.AddInParameter(cmd, "@KTP_NAME", DbType.String, Convert.ToString(dr["KTP_NAME"]));
                            db.AddInParameter(cmd, "@KTP_NO", DbType.String, Convert.ToString(dr["KTP_NO"]));
                            db.AddInParameter(cmd, "@DATE_OF_BIRTH", DbType.DateTime, Convert.ToDateTime(dr["DATE_OF_BIRTH"]));
                            db.AddInParameter(cmd, "@PLACE_OF_BIRTH", DbType.String, Convert.ToString(dr["PLACE_OF_BIRTH"]));
                            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, Convert.ToString(dr["ADDRESS_BUSINESS"]));
                            db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, Convert.ToString(dr["RESIDENTIAL_PHONE"]));
                            db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, Convert.ToString(dr["MOBILE_PHONE"]));
                            db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, Convert.ToString(dr["OFFICE_PHONE"]));
                            db.AddInParameter(cmd, "@PRODUCT_TYPE", DbType.Int16, Convert.ToInt16(dr["PRODUCT_TYPE"]));
                            db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, Convert.ToString(dr["PROGRESS_STATUS"]));
                            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                            db.ExecuteNonQuery(cmd);
                            

                            if ((Convert.ToString(dr["PROGRESS_STATUS"]).Trim() == "PCD" || Convert.ToString(dr["PROGRESS_STATUS"]).Trim() == "CD") && Convert.ToString(dr["RESTRUCT_COMPONENT"]).Trim() == "1")
                            {
                                cmd = db.GetSqlStringCommand(@"UPDATE [TBL_DEDUP_CHECKING_REQ_TMP]
                                     SET  [PROSPECT_NO] = @PROSPECT_NO, [UNIT_CODE] = @UNIT_CODE, [KTP_NAME] = @KTP_NAME,[PLACE_OF_BIRTH] = @PLACE_OF_BIRTH
                                          ,[DATE_OF_BIRTH] = @DATE_OF_BIRTH,[KTP_NO] = @KTP_NO
                                          ,[ADDRESS_BUSINESS] = @ADDRESS_BUSINESS,[MOBILE_PHONE] = @MOBILE_PHONE
                                          ,[RESIDENTIAL_PHONE] = @RESIDENTIAL_PHONE,[OFFICE_PHONE] = @OFFICE_PHONE
                                          ,[PCD_DATE] = @PCD_DATE, [NO_OF_REQUEST] = @NO_OF_REQUEST + 1
                                     WHERE [PROSPECT_ID] = @PROSPECT_ID 

                                     IF @@ROWCOUNT = 0

                                     INSERT INTO [TBL_DEDUP_CHECKING_REQ_TMP]
                                       ([PROSPECT_ID],[PROSPECT_NO],[UNIT_CODE],[KTP_NAME],[PLACE_OF_BIRTH]
                                       ,[DATE_OF_BIRTH],[KTP_NO],[ADDRESS_BUSINESS]
                                       ,[MOBILE_PHONE],[RESIDENTIAL_PHONE],[OFFICE_PHONE]
                                       ,[PCD_DATE],[NO_OF_REQUEST])
                                     VALUES
                                       (@PROSPECT_ID,@PROSPECT_NO,@UNIT_CODE,@KTP_NAME,@PLACE_OF_BIRTH
                                       ,@DATE_OF_BIRTH,@KTP_NO,@ADDRESS_BUSINESS
                                       ,@MOBILE_PHONE,@RESIDENTIAL_PHONE,@OFFICE_PHONE
                                       ,@PCD_DATE,@NO_OF_REQUEST + 1)");
                                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                                db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, Convert.ToString(dr["PROSPECT_NO"]));
                                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                                db.AddInParameter(cmd, "@KTP_NAME", DbType.String, Convert.ToString(dr["KTP_NAME"]));
                                db.AddInParameter(cmd, "@PLACE_OF_BIRTH", DbType.String, Convert.ToString(dr["PLACE_OF_BIRTH"]));
                                db.AddInParameter(cmd, "@DATE_OF_BIRTH", DbType.DateTime, Convert.ToDateTime(dr["DATE_OF_BIRTH"]));
                                db.AddInParameter(cmd, "@KTP_NO", DbType.String, Convert.ToString(dr["KTP_NO"]));
                                db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, Convert.ToString(dr["ADDRESS_BUSINESS"]));
                                db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, Convert.ToString(dr["MOBILE_PHONE"]));
                                db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, Convert.ToString(dr["RESIDENTIAL_PHONE"]));
                                db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, Convert.ToString(dr["OFFICE_PHONE"]));
                                db.AddInParameter(cmd, "@PCD_DATE", DbType.DateTime, Convert.ToDateTime(dr["PCD_DATE"]));
                                db.AddInParameter(cmd, "@NO_OF_REQUEST", DbType.Byte, Convert.ToByte(dr["NO_OF_CALLS"]));
                                db.ExecuteNonQuery(cmd);
                            }*/

                            if (Convert.ToString(dr["OLD_AO_CODE"]).Trim() != "")
                            {
                                cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_PROSPECT_UPDATE WHERE PROSPECT_ID = @PROSPECT_ID; INSERT INTO TBL_PROSPECT_UPDATE (PROSPECT_ID, UPDATE_DATE) VALUES (@PROSPECT_ID, @UPDATE_DATE)");

                                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                                db.ExecuteNonQuery(cmd);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    //CreateErrorLog("enh2 UploadProspect : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public byte[] GetProspectUpdateFromMobile(string table, string unit, string ao_code, string LoginUser, string LoginPass)
        {
            DataSet ds = new DataSet();
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd = db.GetStoredProcCommand("WS_GET_PROSPECT_UPDATE_FROM_SPAMOBILE");

                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@ID", DbType.String, table);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, ao_code);

                ds = db.ExecuteDataSet(cmd);
            }
            return TransformIntoBytes(ds);
        }

        [WebMethod]
        public int UpdateProspectUpdateFromMobile(string[] ids, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand("UPDATE TBL_PROSPECT SET LAST_UPDATE_FROM = '0' WHERE PROSPECT_ID = @PROSPECT_ID");
                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String);
                cmd.CommandTimeout = 1800;
                int result = 0;
                foreach (string id in ids)
                {
                    db.SetParameterValue(cmd, "@PROSPECT_ID", id);
                    result += db.ExecuteNonQuery(cmd);
                }
                return result;
            }
            return -1;
        }

        [WebMethod]
        public string UploadProspectVisitFunding(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_VISIT_FUNDING_TMP 
                                SET 
                                    OBJECTIVE_OF_VISIT = @OBJECTIVE_OF_VISIT
                                    ,DATE_VISIT_PLAN = @DATE_VISIT_PLAN
                                    ,DATE_VISIT_ACTUAL = @DATE_VISIT_ACTUAL
                                    ,JOINT_FIELD_WORK = @JOINT_FIELD_WORK
                                    ,RESULT_OF_VISIT = @RESULT_OF_VISIT
                                    ,REMARK = @REMARK
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                    ,IS_UPLOAD = @IS_UPLOAD
                                    ,IS_DELETED = @IS_DELETED
                                WHERE (VISIT_NO = @VISIT_NO) AND (PROSPECT_ID = @PROSPECT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_VISIT_FUNDING_TMP
                                (
                                    PROSPECT_ID
                                    ,VISIT_NO
                                    ,OBJECTIVE_OF_VISIT
                                    ,DATE_VISIT_PLAN
                                    ,DATE_VISIT_ACTUAL
                                    ,JOINT_FIELD_WORK
                                    ,RESULT_OF_VISIT
                                    ,REMARK
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                    ,IS_UPLOAD
                                    ,IS_DELETED
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@VISIT_NO
                                    ,@OBJECTIVE_OF_VISIT
                                    ,@DATE_VISIT_PLAN
                                    ,@DATE_VISIT_ACTUAL
                                    ,@JOINT_FIELD_WORK
                                    ,@RESULT_OF_VISIT
                                    ,@REMARK
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                    ,@IS_UPLOAD
                                    ,@IS_DELETED
                                );
                            ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@VISIT_NO", DbType.Byte, Convert.ToByte(dr["VISIT_NO"]));
                        db.AddInParameter(cmd, "@OBJECTIVE_OF_VISIT", DbType.String, Convert.ToString(dr["OBJECTIVE_OF_VISIT"]));
                        db.AddInParameter(cmd, "@DATE_VISIT_PLAN", DbType.DateTime, Convert.ToDateTime(dr["DATE_VISIT_PLAN"]));
                        db.AddInParameter(cmd, "@DATE_VISIT_ACTUAL", DbType.DateTime, Convert.ToDateTime(dr["DATE_VISIT_ACTUAL"]));
                        db.AddInParameter(cmd, "@JOINT_FIELD_WORK", DbType.String, Convert.ToString(dr["JOINT_FIELD_WORK"]));
                        db.AddInParameter(cmd, "@RESULT_OF_VISIT", DbType.String, Convert.ToString(dr["RESULT_OF_VISIT"]));
                        db.AddInParameter(cmd, "@REMARK", DbType.String, Convert.ToString(dr["REMARK"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");
                        db.AddInParameter(cmd, "@IS_DELETED", DbType.String, Convert.ToString(dr["IS_DELETED"]));

                        if (db.ExecuteNonQuery(cmd) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    //CreateErrorLog("enh2 UploadProspectVisit : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public byte[] GetPVisitFundingAll(string nip, string pass, string AoCode, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetPVisitFundingAll");

                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public string UploadProspectDocFunding(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_DOCUMENTS_FUNDING_TMP 
                                SET 
                                    ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                    ,IS_UPLOAD = @IS_UPLOAD
                                    ,IS_DELETED = @IS_DELETED
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (DOCUMENT_ID = @DOCUMENT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_DOCUMENTS_FUNDING_TMP
                                (
                                    PROSPECT_ID
                                    ,DOCUMENT_ID
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                    ,IS_UPLOAD
                                    ,IS_DELETED
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@DOCUMENT_ID
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                    ,@IS_UPLOAD
                                    ,@IS_DELETED
                                );
                              ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@DOCUMENT_ID", DbType.Int16, Convert.ToInt16(dr["DOCUMENT_ID"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");
                        db.AddInParameter(cmd, "@IS_DELETED", DbType.String, Convert.ToString(dr["IS_DELETED"]));

                        if (db.ExecuteNonQuery(cmd) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public byte[] GetGoodUntilFunding(string nip, string pass)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(
                    @"  SELECT  GU_FUP_FUNDING, GU_CLOSE_DEAL_FUNDING
                        FROM    TBL_SYS_PARAMS (NOLOCK) ORDER BY UPDATE_DATE DESC");

                DataSet dsSysParams = db.ExecuteDataSet(cmd);
                dsSysParams.Tables[0].TableName = "TBL_SYS_PARAMS";

                using (DataSet ds = new DataSet())
                {
                    DataTable dt3 = dsSysParams.Tables[0].Clone();
                    foreach (DataRow dr in dsSysParams.Tables[0].Rows)
                    {
                        dt3.ImportRow(dr);
                    }

                    ds.Tables.Add(dt3);
                    return TransformIntoBytes(ds);
                }
            }
            return null;
        }

    }
}